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.

Answer 1

Use jsonb_set() inside jsonb_set()

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

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