PosgreSQL: Convert JSON array to JSON object

Question

I need some help in converting JSON array in postgresTO JSON object as below. Please Note the keys are not known in advance, any key can show up in the JSON array. Is there a way to just flatten it out.

INPUT:-

 [{"col1": "1", "col2": null, "col3": "7"}, {"col4": "19"}, {"col5": "19", "col6":"18"}]

OUTPUT:-

{"col1": "1", "col2": null, "col3": "7", "col4": "19", "col5": "19", "col6":"18"}

Thank you

Unrelated to your problem, but: Postgres 9.3 and 9.5 are no longer supported you should plan an upgrade as soon as possible. Additionally: the JSON support in modern Postgres versions is way better than it was back then. — user330315
– user330315, Commented Sep 15, 2022 at 11:41

Stefanov.sm · Accepted Answer · 2022-09-15 11:46:36Z

1

First flatten (using json_array_elements and then json_each on each array element) and then aggregate back with json_object_agg.

select json_object_agg(k, v) 
from json_array_elements
('[
  {"col1":"1","col2":null,"col3":"7"},
  {"col4":"19"},
  {"col5":"19","col6":"18"}
]') as ja 
cross join lateral json_each(ja) as jl(k, v);

edited Sep 15, 2022 at 11:46

answered Sep 15, 2022 at 11:41

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

PosgreSQL: Convert JSON array to JSON object

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