postgresql - Update JSON Array in Postgres with specific key

Question

I have a complex array which look like following in a table column:

{
"sometag": {},
"where": [
    {
        "id": "Krishna",
        "nick": "KK",
        "values": [
            "0"
        ],
        "function": "ADD",
        "numValue": [
            "0"
        ]
    },
    {
        "id": "Krishna1",
        "nick": "KK1",
        "values": [
            "0"
        ],
        "function": "SUB",
        "numValue": [
            "0"
        ]
    }
],
"anotherTag": [],
"TagTag": {
    "tt": "tttttt",
    "tt1": "tttttt"
}

In this array, I want to update the function and numValue of id: "Krishna".

Kindly help.

question from:https://stackoverflow.com/questions/65840360/update-json-array-in-postgres-with-specific-key

Answer 1

This is really nasty because

1. Updating an element inside a JSON array always requires to expand the array
2. On-top: The array is nested
3. The identfier for the elements to update is a sibling not a parent, which means, you have to filter by a sibling

So I came up with a solution, but I want to disclaim: You should avoid doing this as regular database action! Better would be:

1. Parsing your JSON in the backend and do the operations in your backend code
2. Normalize the JSON in your database if that would be a common task, meaning: Create tables with appropriate columns and extract your JSON into the table structure. Do not store entire JSON objects in the database! That would make every single task much more easier and incredible more performant!

demo:db<>fiddle

SELECT
    jsonb_set(                                                                                -- 5
        (SELECT mydata::jsonb FROM mytable), 
        '{where}', 
        updated_array
    )::json
FROM (
    SELECT
        jsonb_agg(                                                                            -- 4
            CASE WHEN array_elem ->> 'id' = 'Krishna' THEN
                jsonb_set(                                                                    -- 3
                    jsonb_set(array_elem.value::jsonb, '{function}', '"ADDITION"'::jsonb),    -- 2
                    '{numValue}', 
                    '["0","1"]'::jsonb
                )
            ELSE array_elem::jsonb END
        ) as updated_array
    FROM mytable,
        json_array_elements(mydata -> 'where') array_elem                                     -- 1
) s

1. Extract the nested array elements into one element per row
2. Replace function value. Note the casts from type json to type jsonb. That is necessary because there's no json_set() function but only jsonb_set(). Naturally, if you just have type jsonb, the casts are not necessary.
3. Replace numValue value
4. Reaggregate the array
5. Replace the where value of the original JSON object with the newly created array object.