How to append nested key-value to json array in postgresql?

Question

My json values in jsonb column are below:

{"Id": "7324326", "UserName":"Henry", "Details": {"Email":"[email protected]", "Phone":"03722", "Images": ["https://face.png"]}}

{"Id": "325325", "UserName":"Mark", "Details": {"Email":"[email protected]", "Phone":"83272", "Images": ["https://hair.png", "https://nose.png"]}}

{"Id": "832743", "UserName":"David", "Details": {"Email":"[email protected]", "Phone":"65128", "Images": []}}

I have nested key - "Images" that has array of strings. How to write query that will add to Images new keys/values ("UploadedBy":null and "Size":null) and transform array of string to key-value ("https://face.png" -> "Link":"https://face.png") like below:

{"Id": "7324326", "UserName":"Henry", "Details": {"Email":"[email protected]", "Phone":"03722", "Images": [{"Link":"https://face.png", "UploadedBy":null, "Size":null}]}}

{"Id": "325325", "UserName":"Mark", "Details": {"Email":"[email protected]", "Phone":"83272", "Images": [{"Link":"https://hair.png", "UploadedBy":null, "Size":null}, {"Link":"https://nose.png", "UploadedBy":null, "Size":null}]}}

{"Id": "832743", "UserName":"David", "Details": {"Email":"[email protected]", "Phone":"65128", "Images": []}}

Ajax1234 · Accepted Answer · 2022-07-18 20:13:47Z

1

You can use jsonb_set:

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

See fiddle.

answered Jul 18, 2022 at 20:13

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to append nested key-value to json array in postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related