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

Question

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

Bergi · Accepted Answer · 2022-07-05 12:09:18Z

2

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)}

edited Jul 5, 2022 at 12:09

answered Jul 4, 2022 at 15:29

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shay Zambrovski Over a year ago

looks great, but with store1 is dynamically changed? i.e., how it can be $2?

Shay Zambrovski Over a year ago

look great, thanks mate!, this is the query: (same as in the dbfiddle, which is a great tool)

UPDATE fruit SET data = jsonb_set(data, ARRAY[key], jsonb_set(json_value, '{first_added}', data->key->'first_added')) FROM (VALUES('store1', '{"price": "12", "store_id": "1", "comments": "some comments...V2"}'::jsonb)) AS vals(key, json_value) WHERE id = 1

;

emomaliev · Accepted Answer · 2022-07-04 16:02:07Z

0

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

edited Jul 4, 2022 at 16:02

answered Jul 4, 2022 at 11:25

emomaliev

2,4233 gold badges12 silver badges17 bronze badges

4 Comments

Shay Zambrovski Over a year ago

thanks, but I don't want to be depends in the store fields? i.e. those fields can be changed (added or removed), override all fields expects the first_added field

emomaliev Over a year ago

@ShayZambrovski But you are not dependent on store fields. If you add your fields to jsonb_build_object, they will also be added as new ones. Just if you specify those fields that are already in the store, then they are updated

emomaliev Over a year ago

@ShayZambrovski I understand you, you want to be able to change all the data of the store, but keep the first_added field, if so, then see my modified answer)

Shay Zambrovski Over a year ago

But still, I need to "know" all the fields, I want to avoid it.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related