json - How to change jsonb object array value for entire table?

Question

i have a table with a JSONB column called "columnsettings". The jsonb data looks like:.

[
    {
        "data": "id",
        "width": 25
    },
    {
        "data": "field_1",
        "width": 125
    },
    {
        "data": "field_3",
        "width": 183
    },
    {
        "data": "field_11",
        "width": 125
    }
]

I now want to run an update on all rows in the table and change the "data" attribute ='newvalue' where the data value ='field_1" for example: The expected result for all rows would be:

[
    {
        "data": "id",
        "width": 25
    },
    {
        "data": "newvalue",
        "width": 125
    },
    {
        "data": "field_3",
        "width": 183
    },
    {
        "data": "field_11",
        "width": 125
    }
]

I can't seem to find the right syntax to update this specific array value in all rows of table. Any help is appreciated.

question from:https://stackoverflow.com/questions/66051494/how-to-change-jsonb-object-array-value-for-entire-table

Answer 1

You can use JSONB_SET() function nested in JSONB_AGG() within an UPDATE Statement containing a conditional, after splitting the elements of the array through applying JSONB_ARRAY_ELEMENTS() function such as

UPDATE tab
   SET columnsettings =
   (
    SELECT JSONB_AGG(CASE WHEN j ->> 'data' = 'field_1' THEN 
                               JSONB_SET(j, '{data}', '"newvalue"')
                          ELSE
                               j
                           END)    
      FROM JSONB_ARRAY_ELEMENTS(columnsettings) AS j
   )

Demo