PosgreSQL: Convert JSON array to JSON object-CodePudding

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

CodePudding user response：

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