Update object field of element in array jsonb with postgres

Question

I have following jsonb column which name is data in my sql table.

{
    "special_note": "Some very long special note",
    "extension_conditions": [
            {
                "condition_id": "5bfb8b8d-3a34-4cc3-9152-14139953aedb",
                "condition_type": "OPTION_ONE"
            },
            {
                "condition_id": "fbb60052-806b-4ae0-88ca-4b1a7d8ccd97",
                "condition_type": "OPTION_TWO"
            }
        ],
    "floor_drawings_file": "137c3ec3-f078-44bb-996e-161da8e20f2b",
}

What I need to do is to update every object's field with name condition_type in extension_conditions array field from OPTION_ONE to MARKET_PRICE and OPTION_TWO leave the same.

Consider that this extension_conditions array field is optional so I need to filter rows where extension_conditions is null

I need a query which will update all my jsonb columns of rows of this table by rules described above.

Thanks in advance!

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2021-02-02T18:15:29

You can use such a statement containing JSONB_SET() function after determining the position(index) of the related key within the array

WITH j AS
(
 SELECT ('{extension_conditions,'||idx-1||',condition_type}')::TEXT[] AS path, j
   FROM tab 
  CROSS JOIN JSONB_ARRAY_ELEMENTS(data->'extension_conditions') 
   WITH ORDINALITY arr(j,idx)
  WHERE j->>'condition_type'='OPTION_ONE' 
)
UPDATE tab
   SET data = JSONB_SET(data,j.path,'"MARKET_PRICE"',false)
  FROM j

Demo 1

Update : In order to update for multiple elements within the array, the following query containing nested JSONB_SET() might be preferred to use

UPDATE tab
   SET data =
   (
   SELECT JSONB_SET(data,'{extension_conditions}',
                    JSONB_AGG(CASE WHEN j->>'condition_type' = 'OPTION_ONE'
                                THEN JSONB_SET(j, '{condition_type}', '"MARKET_PRICE"')
                                ELSE j
                              END))
     FROM JSONB_ARRAY_ELEMENTS(data->'extension_conditions') AS j
   )
WHERE data @> '{"extension_conditions": [{"condition_type": "OPTION_ONE"}]}';

Demo 2

Update object field of element in array jsonb with postgres

1 Answers