Query to update the JSON value

Question

I want to query to update a table that has JSON data. The data of the column detail in the table details are as follows.

id     | 1

detail | {
   myCompany: [{ email: 'company@company.com', inProfit: true }],
   myData: [
      { email: 'example@example.com'},
      { email: 'test@test.com'},
      { email: 'test@example.com' },
   ],
};

The JSON value of the column detail is as follows

{
   "myCompany":[
      {
         "email":"company@company.com",
         "inProfit":true
      }
   ],
   "myData":[
      {
         "email":"example@example.com"
      },
      {
         "email":"test@test.com"
      },
      {
         "email":"test@example.com",
      }
   ]
} 

I want a query that updates the myData. Add a new field personalUse:true for all fields of myData after email. For example, after running the update query the myData field should look like as follows,

"myData":[
      {
         "email":"example@example.com",
         "personalUse":true
      },
      {
         "email":"test@test.com",
         "personalUse":true
      },
      {
         "email":"test@example.com",
         "personalUse":true
      }
   ]

You can use the below queries to try it locally.

CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail) 
VALUES 
  ('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],  
     "myData":[{"email":"example@example.com" }, 
               {"email":"test@test.com"},
               {"email":"test@example.com"} ] }');

Please, someone, help me out.

Answer 1

There are several functions that may be helpful:

• -> operator: gets JSON object field by key.
• jsonb_agg: aggregates values as a JSON array.
• json_array_elements: expands a JSON array to a set of JSON values.
• jsonb_set: replaces part of JSON designated by path with a value (or inserts it if the part is missing).

You can read more here:

• Aggregate Functions
• JSON Functions and Operators

Query

update details
  set detail = jsonb_set(detail::jsonb, '{myData}', myData)
  from (
    select id, jsonb_agg(myData) as myData
      from (
        select
          id,
          jsonb_set(
            json_array_elements(detail->'myData')::jsonb,
            '{personalUse}',
            'true') as myData
          from details
      ) x
      group by id
  ) z
  where details.id = z.id;

How it works

Expand myData arrays along with id into table.

user=# select id, json_array_elements(detail->'myData') as myData from details;
 id |                        mydata                         
----+-------------------------------------------------------
  1 | {"email": "example@example.com"}
  1 | {"email": "test@test.com"}
  1 | {"email": "test@example.com"}
  2 | {"email": "tt@test.com"}
  2 | {"email": "tt@example.com"}

Use jsonb_set to update each object.

user=# select
user-#   id,
user-#   jsonb_set(
user(#     json_array_elements(detail->'myData')::jsonb,
user(#     '{personalUse}',
user(#     'true'
user(#   ) as myData
user-# from details;
 id |                        mydata                         
----+-------------------------------------------------------
  1 | {"email": "example@example.com", "personalUse": true}
  1 | {"email": "test@test.com", "personalUse": true}
  1 | {"email": "test@example.com", "personalUse": true}
  2 | {"email": "tt@test.com", "personalUse": true}
  2 | {"email": "tt@example.com", "personalUse": true}

Use jsonb_agg to collect emails back into JSON array.

user=# select id, jsonb_agg(myData) as myData from (
user(#   select
user(#     id,
user(#     jsonb_set(
user(#       json_array_elements(detail->'myData')::jsonb,
user(#       '{personalUse}',
user(#       'true'
user(#     ) as myData
user(#   from details
user(# ) x group by id;
 id |
----+-------------------------------------------------------------
  2 | [{"email": "tt@test.com", "personalUse": true}, ...
  1 | [{"email": "example@example.com", "personalUse": true}, ...

Now you can use jsonb_set again to update original JSON.