Sample Header Ad - 728x90

Query to update the JSON that does not contain a specific key

0 votes
1 answer
615 views
I want to query 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', personalUse: true },
      { email: 'test@test.com', personalUse: false },
      { 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",
         "personalUse":true
      },
      {
         "email":"test@test.com",
         "personalUse":false
      },
      {
         "email":"test@example.com",
      }
   ]
}
I want a query that updates the myData. If the myData field doesn't contain personalUse then update that field with personalUse:true. In the given example { email: 'test@example.com' } field should update as { 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", "personalUse":true }, 
               {"email":"test@test.com", "personalUse":false },
               {"email":"test@example.com"} ] }');
Please, someone, help me out.
Asked by Rich (1 rep)
Apr 23, 2020, 03:27 PM
Last activity: Aug 8, 2025, 10:08 PM