Why is Postgres jsonb_agg function wrapping the output into additional JSON field?
SELECT jsonb_agg(t) AS json_array
FROM (SELECT jsonb_set('[{"f1": "1","f2": null}, 2, null, 3]', '{0,f1}', '"DELETEME"',true)) t
And it returns this:
[{"jsonb_set": [{"f1": "DELETEME", "f2": null}, {"f3": "1", "f4": null}]}]
How to avoid such behaviour? As I need it to return:
[{"f1": "DELETEME", "f2": null}, {"f3": "1", "f4": null}]
You are converting the whole table alias t
to json instead of just that one column. Change t
to t.jsonb_set
. This works:
=# SELECT jsonb_agg(t.jsonb_set) AS json_array
FROM (SELECT jsonb_set('[{"f1": "1","f2": null}, 2, null, 3]', '{0,f1}', '"DELETEME"',true)) t;
json_array
------------------------------------------------
[[{"f1": "DELETEME", "f2": null}, 2, null, 3]]
(1 row)