Update array of objects in JSONB format PostgreSQL

Question

I need to perform two update operations:

update change data from string to bool in first case
recreate tag with new name and data based on other tag

sample data in jsonb column:

[
    { "tax": "yes", "tax_percent": 20, "used_when": "after" },
    { "tax": "no", "tax_percent": 20 },
    { "tax_percent": 20, "used_when": "before" }
]

and now:

"tax" value needs to be updated from yes -> true, no OR null (not exists) means -> false

"used_when" needs to be updated to "using" and if after -> true, if before OR null (not exists) means -> false

so it will look like:

[
    { "tax": true, "tax_percent": 20, "using": true },
    { "tax": false, "tax_percent": 20, "using": false },
    { "tax": false, "tax_percent": 20, "using": false }
]

The values are optional so not all entries will have it, also this is single row in database column so this data in column needs to be updated for each row.

This would be so much easier with a properly normalized model — a_horse_with_no_name

S-Man S-Man · Accepted Answer · 2021-01-21T15:07:15

step-by-step demo:db<>fiddle

UPDATE t                                                                   -- 5
SET mydata = s.new_data
FROM (
    SELECT
        id,
        json_agg((using_updated - 'used_when')::json) as new_data          -- 4
    FROM t,
        json_array_elements(t.mydata) as elements,                         -- 1
        jsonb_set(elements::jsonb, '{tax}',                                -- 2
            CASE
                WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
                ELSE 'false'::jsonb
            END
        ) as tax_updated,
        jsonb_set(tax_updated::jsonb, '{using}',                           -- 3
            CASE
                WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb
                ELSE 'false'::jsonb
            END
        ) as using_updated
    GROUP BY id
) s
WHERE s.id = t.id;

Extract all array elements into one element per record 2/3. Now you can use jsonb_set() to insert new or update existing attributs within the array elements. The CASE clause does the condition check
Eliminate the remaining used_when elements. Afterwards you can reaggregate the updated elements with json_agg
Do the UPDATE

If your data is type json, you have to do the casts to jsonb since, there is no json_set(). If not, you can ignore the casts, of course.

However, as @a_horse_with_no_name correctly mentioned: You should think about not storing these data as pure JSON but extract them into a normalized relative database table structure, which make things much easier and more performant.

Update array of objects in JSONB format PostgreSQL

1 Answers