How to update entire JSON object in JSONB postgres column except 1 field-CodePudding

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

CodePudding user response：

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"}}.

CodePudding user response：

You can use the jsonb_set function to change the desired element, then use the jsonb_build_object function to create a new dataset, then concatenate the data with the || operator to keep the rest of the data(first_added,...)

update table1 
  set data = jsonb_set(data, '{store1}',  jsonb_build_object('first_added', data->'store1'->'first_added', 'price', 12, 'store_id', 1, 'comments', 'some comments...V2'))
where id  = 1;

Demo in DBfiddle