By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table house (sale json);
insert into house values ('{"houses": [{"houseId": "house100", "houseLocation": "malvern", "attribute": {"colour": ["white", "grey"], "openForInspection": {"fromTime": "0001", "toTime": "2359"}}, "priceRange": null}]}')
1 rows affected
select jsonb_build_object('houses', (select array_agg(case when (v.value -> 'priceRange')::text = 'null' then v.value::jsonb || '{"priceRange":{"fromAmount": "100","toAmount": "1000"}}'::jsonb else v.value::jsonb end) from json_array_elements(h.sale -> 'houses') v)) from house h
| jsonb_build_object |
|---|
| {"houses": [{"houseId": "house100", "attribute": {"colour": ["white", "grey"], "openForInspection": {"toTime": "2359", "fromTime": "0001"}}, "priceRange": {"toAmount": "1000", "fromAmount": "100"}, "houseLocation": "malvern"}]} |