How to flatten an array of nested objects in Postgres

Question

I have a rows that have json formatted as

[{"FIRST": {"0": 1580, "1": 1745, "2": 2050, "3": 2395}}, {"SECOND": {"1": 1895, "2": 2431}}]

Because of the way the SQL was written there is only ever one toplevel key. I don't want to hardcode anything. I've tried a few solutions and they've led to me dropping values. I'm looking for a way to do this in pure Postgres that these objects get flattened and the array gets removed.

Expected output:

{"FIRST": {"0": 1580, "1": 1745, "2": 2050, "3": 2395}, "SECOND": {"1": 1895, "2": 2431}}

Anyone have an idea how I could go about doing this?

Answer 1

This should be pretty simple with jsonb_array_elements, jsonb_each and jsonb_object_agg:

SELECT jsonb_object_agg(key, value)
FROM example_table t,
jsonb_array_elements(t.json_col) AS elements(el),
jsonb_each(el) AS pairs(key, value)

^{(online demo)}

You'll want to either GROUP BY some row identifier or select only a single row with a WHERE clause. Alternatively use a subquery:

SELECT *, (
    SELECT jsonb_object_agg(key, value)
    FROM jsonb_array_elements(t.json_col) AS elements(el),
    jsonb_each(el) AS pairs(key, value)
) AS new_object
FROM example_table t