Store and update jsonb value in Postgres

Question

I have a table such as:

ID | Details
1  | {"name": "my_name", "phone": "1234", "address": "my address"}
2  | {"name": "his_name", "phone": "4321", "address": "his address"}

In this, Details is a jsonb object. I want to add another field named 'tags' to jsonb which should have some particular keys. In this case, "name", "phone". The final state after execution of the query should be:

 ID | Details
 1  | {"tags": {"name": "my_name", "phone": "1234"},"name": "my_name", "phone": "1234", "address":"my address"}
 2  | {"tags": {"name": "his_name", "phone": "4321"},"name": "his_name", "phone": "4321", "address":"his address"}

I can think of the following steps to get this done:

Loop over each row and extract the details["name"] and details["phone"] in variables.
Add these variables to the jsonb.

I cant think of how the respective postgres query for this should be. Please guide.

Kaushik Nayak · Accepted Answer · 2019-03-22 07:19:25Z

2

use jsonb_build_object

update t set details 
               = jsonb_build_object ( 'tags',
            jsonb_build_object( 'name', details->>'name', 'phone',details->>'phone')
                  ) 
|| details

DEMO

answered Mar 22, 2019 at 7:19

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

Chris Bandy · Accepted Answer · 2019-03-22 07:07:43Z

1

Use the concatenate operator, of course!

https://www.postgresql.org/docs/current/functions-json.html

update t1 set details = details || '{"tags": {"name": "my_name"}}' where id = 1

answered Mar 22, 2019 at 7:07

Chris Bandy

1,61814 silver badges7 bronze badges

Comments

user330315 · Accepted Answer · 2019-03-22 07:17:29Z

0

You can extract the keys you are interested in, build a new json value and append that to the column:

update the_table
  set details = details || jsonb_build_object('tags', 
                               jsonb_build_object('name', details -> 'name', 
                                                  'phone', details -> 'phone'));

answered Mar 22, 2019 at 7:17

user330315

Collectives™ on Stack Overflow

Store and update jsonb value in Postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related