How do I SELECT a column of json objects in Postgres so that the rows returned are json arrays of arrays?

Question

I have a table with a single jsonb column. How do I perform a select query that transforms each jsonb row into a jsonb array where each item is another array with the first index being the key and the second index being the value? The keys are not known ahead of time.

Selecting the column with this query:

SELECT myRow FROM myTable

returns rows with the following jsonb values:

{
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4",
    "key5": "value5"
}

I want the output rows to look like this:

[
    ["key1", "value1"],
    ["key2", "value2"],
    ["key3", "value3"],
    ["key4", "value4"],
    ["key5", "value5"]
]

Jeremy Jeremy · Accepted Answer · 2019-07-01T16:19:01

S-Man's query will merge all rows from the original table into one big array. It's a bit unclear from your question if that's what you want or not. If you instead want each row to have its own array, you could do something like this:

select arr 
from data 
join lateral(
    select jsonb_agg(jsonb_build_array(key, value)) arr 
    from jsonb_each(j)
) sub on true;

Here's an example using some data from a CTE:

with data(j) as (
  select '{
    "key1": "value1",
    "key2": "value2",
    "key3": "value3",
    "key4": "value4",
    "key5": "value5"
  }'::jsonb 
  UNION select '{"key6": "value6"}'
)
select arr 
from data 
join lateral(
  select jsonb_agg(jsonb_build_array(key, value)) arr 
  from jsonb_each(j)
) sub on true;
                                                 arr
------------------------------------------------------------------------------------------------------
 [["key6", "value6"]]
 [["key1", "value1"], ["key2", "value2"], ["key3", "value3"], ["key4", "value4"], ["key5", "value5"]]
(2 rows)

How do I SELECT a column of json objects in Postgres so that the rows returned are json arrays of arrays?

2 Answers