Postgres 14

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 tbl (js jsonb);

insert into tbl values ('{"Id": "7324326", "UserName":"Henry", "Details": {"Email":"henry@test.com", "Phone":"03722", "Images": ["https://face.png"]}}'), ('{"Id": "325325", "UserName":"Mark", "Details": {"Email":"mark@test.com", "Phone":"83272", "Images": ["https://hair.png", "https://nose.png"]}}'), ('{"Id": "832743", "UserName":"David", "Details": {"Email":"david@test.com", "Phone":"65128", "Images": []}}');

CREATE TABLE

INSERT 0 3

update tbl set js = jsonb_set(js, '{Details,Images}'::text[],

coalesce((select jsonb_agg(jsonb_build_object('Link', v.value, 'UploadedBy', null, 'Size', null))

from jsonb_array_elements(js -> 'Details' -> 'Images') v), '[]'::jsonb));

select * from tbl;

UPDATE 3

js
{"Id": "7324326", "Details": {"Email": "henry@test.com", "Phone": "03722", "Images": [{"Link": "https://face.png", "Size": null, "UploadedBy": null}]}, "UserName": "Henry"}
{"Id": "325325", "Details": {"Email": "mark@test.com", "Phone": "83272", "Images": [{"Link": "https://hair.png", "Size": null, "UploadedBy": null}, {"Link": "https://nose.png", "Size": null, "UploadedBy": null}]}, "UserName": "Mark"}
{"Id": "832743", "Details": {"Email": "david@test.com", "Phone": "65128", "Images": []}, "UserName": "David"}

SELECT 3