postgres jsonb_set multiple keys update

Question

I have DB table with jsonb column.

number  | data
    1   | {"name": "firstName", "city": "toronto", "province": "ON"}

I need a way to update data column. So my output should look like:

{"name": "firstName", "city": "ottawa", "province": "ON", "phone": "phonenum", "prefix": "prefixedName"}

Is it possible with json_set? I have added query like:

update table_name set data = jsonb_set(data, '{city}', '"ottawa"') where number = 1;

However, I need a way to add new key-value if it does not exists and update key value if it exists. Is it possible to achieve this in single query?

Abelisto · Accepted Answer · 2020-12-09 17:42:27Z

181

The documentation says:

The || operator concatenates the elements at the top level of each of its operands. ... For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.

So using your example data:

update table_name set
    data = data || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}'
    where number = 1;

Additionally if the object you want to edit is not at the top level - just combine the concatenation and jsonb_set function. For example, if the original data looks like

{"location": {"name": "firstName", "city": "toronto", "province": "ON"}}

then

...
data = jsonb_set(
    data, 
    '{location}', data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}')
...

edited Dec 9, 2020 at 17:42

answered Aug 10, 2016 at 22:28

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

6 Comments

stack_d_code Over a year ago

Awesome, this works like charm. Thanks! I am not able to up-vote it just yet because I dont have enough points, but definitely thumbs up.

Abelisto Over a year ago

@MarcoPrins Have you tried to read the Oracle documentation? Compared to it the PostgreSQL documentation is a literary masterpiece :o)

U R Over a year ago

sometimes when you get the error : unknown signature: jsonb_set(jsonb, string, jsonb) it is due to typecasting. Adding ::TEXT[] as shown below solved the issue in my case ... data = jsonb_set(data, '{location}' ::TEXT[] , data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}' ::jsonb) ...

mmigdol Over a year ago

Helpful hint if you are populating the updated fields from dynamic data within your query, and/or updating multiple fields.. You can use json_build_object to create the update object. For example: data->'location' || jsonb_build_object('city', "cityColName", 'phonenum', "phoneCalName"). This is much easier than trying to dynamically creating jsonb strings as @Paarth suggests below.

dynamo Over a year ago

I am not able use JPA Query to update multiple jsonb column fields, any help ?

|

Paarth · Accepted Answer · 2016-08-11 08:09:57Z

1

You can try this

Here we are using jsonb concatation operator || to Concatenate two jsonb objects

update table_name set data = (select val from (
(select 
CASE WHEN data ? key THEN jsonb_set(data, '{' || key || '}', quote_nullable(updated_value))
ELSE 
data || ('{' || quote_ident(key) || ':' || quote_ident(some_value) || '}')::jsonb
END val
 from json_each_text((select data::json from tbl))
CROSS JOIN tbl t
where key in ('city','phone','prefix') and number=1)) where number=1

answered Aug 11, 2016 at 8:09

Paarth

5903 silver badges11 bronze badges

1 Comment

Paarth Over a year ago

please refer [jsonb in postgres] (postgresql.org/docs/9.6/static/functions-json.html)

Collectives™ on Stack Overflow

postgres jsonb_set multiple keys update

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related