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

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 <> '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 <> '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)