Query to update the JSON that does not contain a specific key 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.
I had fun with this one :) This leaves personalUse as-is when present, otherwise sets it to true, and should preserve all other key/values in in the toplevel object besides myData and all keys other than personalUse in the innermost objects. What it is not - and I'm pretty sure cannot be - is remotely efficient, but that can't really be helped without using jsonb, adding indexes, and otherwise doing a lot of high-touch things. This should also work back to at least PG9.5, although I only tested as far back as 10.

testdb=# 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"} ] }');
CREATE TABLE
INSERT 0 1
testdb=# with                                                
explode_fields as (
select id, key as details_key, value as details_value from details, json_each(detail)
),
only_mydata as (
select * from explode_fields where details_key='myData'
),
explode_mydata as (
select only_mydata.*, row_number() over (partition by id) as item_idx, item as mydata_item
FROM only_mydata, json_array_elements(details_value) as item
),
explode_mydata_fields as (
select explode_mydata.*, key as mydata_item_key, value as mydata_item_value
from explode_mydata, json_each(mydata_item)
),
only_mydata_personaluses as (
select * from explode_mydata_fields where mydata_item_key = 'personalUse'
),
/* Need a true for every inner item to use as the 2nd argument to coalesce() */
personaluse_all_trues as (
select id, details_key, details_value, item_idx, mydata_item, 'personalUse' as mydata_item_key, 'true'::json as mydata_item_value
from explode_mydata
),
merged_personaluses as (
select t0.id, t0.details_key, t0.details_value, t0.item_idx, t0.mydata_item, 'personalUse' as mydata_item_key, coalesce(t1.mydata_item_value, t0.mydata_item_value) mydata_item_value
FROM personaluse_all_trues t0
left join only_mydata_personaluses t1
on t0.id=t1.id and t0.details_key=t1.details_key and t0.details_value::text=t1.details_value::text and t0.item_idx::text=t1.item_idx::text and t0.mydata_item::text=t1.mydata_item::text
),
include_other_item_fields as (
select id, details_key, details_value, item_idx, mydata_item, mydata_item_key, mydata_item_value from merged_personaluses
union all
select id, details_key, details_value, item_idx, mydata_item, mydata_item_key, mydata_item_value from explode_mydata_fields where mydata_item_key &lt;&gt; 'personalUse'
),
agg_mydata_fields as (
select id, details_key, details_value::text, item_idx, json_object_agg(mydata_item_key, mydata_item_value) as item
from include_other_item_fields
group by 1, 2, 3, 4
),
agg_mydatas as (
select id, details_key, json_agg(item) as new_details
from agg_mydata_fields group by 1, 2
),
include_other_fields as (
select id, details_key, new_details as details_value
from agg_mydatas
union all
select * from explode_fields where details_key &lt;&gt; 'myData'
),
agg_records as (
select id, json_object_agg(details_key, details_value) as detail from include_other_fields
group by id
)
update details set detail=agg_records.detail
from agg_records
where details.id=agg_records.id;
UPDATE 1
testdb=# select id, jsonb_pretty(detail::jsonb) from details;
 id |                jsonb_pretty                 
----+---------------------------------------------
  1 | {                                          +
    |     "myData": [                            +
    |         {                                  +
    |             "email": "example@example.com",+
    |             "personalUse": true            +
    |         },                                 +
    |         {                                  +
    |             "email": "test@test.com",      +
    |             "personalUse": false           +
    |         },                                 +
    |         {                                  +
    |             "email": "test@example.com",   +
    |             "personalUse": true            +
    |         }                                  +
    |     ],                                     +
    |     "myCompany": [                         +
    |         {                                  +
    |             "email": "company@company.com",+
    |             "inProfit": true               +
    |         }                                  +
    |     ]                                      +
    | }
(1 row)