postgresql: jsonb update multiple keys in one query

Question

I have the following postgresql rows as JSONB row:

{age:26}

And I would like to replace it so that that i looks like this:

{age: 30, city: "new york city"}

How can I do this in postgressql? Someone metioned using jsonb_set(), but I haven't seen any examples of updating multiple keys in one query.

[postgres jsonb_set multiple keys update](http://stackoverflow.com/q/38883233/593144) — Abelisto, Oct 31 '16 at 18:13

score 34 · Answer 1 · answered Dec 18 '16 at 14:21

34

Use jsonb_set() inside jsonb_set()

jsonb_set(jsonb_set('{age:26}'::jsonb,'{age}','"30"'::jsonb)::jsonb,'{city}','"new york city"'::jsonb)

answered Dec 18 '16 at 14:21

SHAKU

657
5
15

1

The standard way of updating multiple keys stated in the answer [here](https://stackoverflow.com/questions/38883233/postgres-jsonb-set-multiple-keys-update) did not help while this answer was perfect for my case as I needed to update multiple keys where the one is a normal value and the other is a jsonb array. – uLan Oct 26 '17 at 04:31

score 5 · Answer 2 · answered Mar 04 '21 at 19:55

5

although you can just nest jsonb_set actions it becomes pretty terrible to read.

Instead, you can use jsonb_object

SET my_column = my_column || jsonb_object(
    array[ -- keys
        'age',
        'city',
        'visits'
    ],
    array[ -- values
        31::text,
        'Johannesburg'::text,
        ((my_column#>>'{visits}')::int + 1)::text -- increment
    ]
)

Note: you will lose type safety since it only handles text fields, but you can do partial updates (only add the fields you want to update) and if you are handling this from another language it tends to be pretty programmable if your SQL abstraction is not too prescriptive

answered Mar 04 '21 at 19:55

WiR3D

1,465
20
23

this is great for text values, but it doesn't work with json values. – anakha Mar 04 '21 at 21:56
yes, you lose type safety. but even with counters, I have found it to be a minor inconvenience. This has allowed me to use one column for dynamically adding new changeable statistics without a schema update. – WiR3D Mar 05 '21 at 14:42

anakha · Answer 3 · 2021-03-04T22:32:44.520

5

Postgresql is great. You can also use the string concatenation operator, ||

UPDATE wooden_table
SET doc = doc
    || '{"color" : "red"}' 
    || '{"hardness" : "1H"}';

This method will also work with JSON values inside the value side.

edited Mar 04 '21 at 22:32

answered Mar 04 '21 at 22:03

anakha

980
6
9

score 0 · Answer 4 · answered Jun 30 '21 at 11:03

With sqlalchemy:

from sqlalchemy import func, and_, any_, cast
from sqlalchemy.dialects.postgresql import JSONB


db.session.query(Model).filter(
    Model.id == any_(ids)
).update({
    Model.your_jsonb_field: cast(
        Model.your_jsonb_field,
        JSONB,
    ).concat(
        func.jsonb_build_object('key1', 'value1'),
    ).concat(
        func.jsonb_build_object('key2', 'value2'),
    )
}, synchronize_session='fetch')

score 0 · Answer 5 · answered Sep 23 '22 at 05:15

0

you can use jsonb_build_object.

jsonb_build_object('age', 30, 'city', 'new york city')

answered Sep 23 '22 at 05:15

jqgsninimo

6,562
1
36
30

score 0 · Answer 6 · answered Oct 21 '22 at 10:11

 UPDATE tablename
 SET name = ?,
 jsonCOl = jsonCol::jsonb || '{"color" : "red"}'::jsonb
 WHERE id = ?

this solution works great as it typecastsboth the saved json and overriding json into jsonb and saves it by replaceing all keys on right hand side of || operator.

score -1 · Answer 7 · edited Mar 18 '20 at 21:50

While updating data this you can use the jsonb column and also create json format data from the attributes you have, There are four things to remember while doing jsonb:

pass first parameter to the jsonb_set function is a target(where you wnt to replace)
The json Key
json value
if it new column make true

select jsonb_set(jsonb_set('{}'::jsonb,'{age}','30',true)::jsonb,'{city}',to_jsonb('hyd'::text),true)::jsonb;

postgresql: jsonb update multiple keys in one query

7 Answers7