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.

Charkha

postgres jsonb_set multiple keys update – Lowermost

Answer 1

Use jsonb_set() inside jsonb_set()

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

Brownstone

The standard way of updating multiple keys stated in the answer here 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. – Ostensible

Answer 2

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

Rhythmics

this is great for text values, but it doesn't work with json values. – Infinite

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. – Rhythmics

Answer 3

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.

Infinite

Answer 4

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

Goody

Answer 5

you can use jsonb_build_object.

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

Bespangle

Answer 6

 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.

Pilsudski

Answer 7

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:

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

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

Saleable