[FIXED] How to update entire JSON object in JSONB postgres column except 1 field

Issue

for example I have a table:

CREATE TABLE fruit(id bigint, data jsonb);

and a row for example is:

1,    
{
   "type": "pinapple",
   "store1": {
   "first_added": "<some timestamp>",
   "price": "10",
   "store_id": "1",
   "comments": "some comments..."
},
   "store2": {
   "first_added": "<some timestamp>",
   "price": "11",
   "store_id": "2",
   "comments": "some comments..."
},
   .... more stores
}

In case of update I have the fruit id and store data :

1,
"store1": {
            "price": "12",
            "store_id": "1",
            "comments": "some comments...V2"
        }

I want to update entire store object in fruit entry (for store1), except the first_added field.

Any idea how I can accomplish it via JSONB operators or functions?

Thanks

Solution

You can use

UPDATE fruit
SET data = data || jsonb_set($1::jsonb, '{store1,first_added}', data#>'{store1,first_added}')
WHERE id = 1;

^{(online demo)}
where the parameter $1 is set to the value {"store1": {"price": "12", "store_id": "1", "comments": "some comments...V2"}}.

Or if you need the key to be dynamic, use

UPDATE fruit
SET data = jsonb_set(data, ARRAY[$2::text], jsonb_set($1::jsonb, '{first_added}', data->$2->'first_added'))
WHERE id = 1;

^{(online demo)}

Answered By - Bergi

Answer Checked By - Marilyn (PHPFixing Volunteer)