Search code examples
postgresqljsonb

Store and update jsonb value in Postgres

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.

Solution

  • use jsonb_build_object

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

    DEMO