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 · Accepted Answer · 2019-07-01 16:19:01Z

1

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)

answered Jul 1, 2019 at 16:19

Jeremy

6,77322 silver badges21 bronze badges

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

3 Comments

Eric Weiler Over a year ago

You are correct, I should have been more clear. Thank you.

Eric Weiler Over a year ago

What does sub on true do at the end?

Jeremy Over a year ago

sub is just the required alias for the subselect. on true is the join condition, also required, which should always be true for this lateral join

S-Man · Accepted Answer · 2019-07-01 16:12:24Z

1

step-by-step demo:db<>fiddle

SELECT
    json_agg(arr)
FROM
    my_table,
    json_each(my_row),
    json_build_array(key, value) as arr

json_each expands your json object into one row per attribute. It creates one column for keys and one for values.
json_build_array creates the inner arrays for each record
json_agg aggregates the arrays from (2) into a huge one.

edited Jul 1, 2019 at 16:12

answered Jul 1, 2019 at 16:06

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related