How to unnest multiple postgres jsonb arrays in parallel

Question

I want to iterate over the elements of two jsonb arrays at once. If it was only one array I know I could simply use json_array_elements and everything would be fine. But for multiple arrays I'm having a hard time to come up with a solution.

Similar to Unnest multiple arrays in parallel, but just for jsonb arrays. So I know that for regular postgres arrays there is unnest(anyarray, anyarray [, ...]).

Here's a snippet of my schematic data scenario:

SELECT *
FROM jsonb_to_recordset('[{"id": 0, "array_a": ["a", "c"], "array_b": ["b" , "d"]}]') 
AS example(id jsonb, array_a jsonb, array_b jsonb)

That I want to transform to:

a | b

c | d

Hope someone can help me with that.

Best wishes Andy

Mike Organek · Accepted Answer · 2021-02-07T12:22:08.110

Use with ordinality and a join to zip the values together:

with invars as (
  SELECT *
    FROM jsonb_to_recordset('[{"id": 0, "array_a": ["a", "c"], "array_b": ["b" , "d"]}]') 
      AS example(id jsonb, array_a jsonb, array_b jsonb)
)
select a1, b1
  from invars
       cross join lateral jsonb_array_elements(array_a) with ordinality as ela(a1, rn)
       cross join lateral jsonb_array_elements(array_b) with ordinality as elb(b1, rn)
 where ela.rn = elb.rn;

Working fiddle.

If the arrays can be of differing lengths, then this works even though there is likely a more elegant solution:

with invars as (
  SELECT *
    FROM jsonb_to_recordset('[{"id": 0, "array_a": ["a", "c", "e"], "array_b": ["b" , "d", "f", "h"]}]')
      AS example(id jsonb, array_a jsonb, array_b jsonb)
), a_side as (
  select a1, rn
    from invars
         cross join lateral jsonb_array_elements(array_a) with ordinality as ela(a1, rn)
), b_side as (
  select b1, rn
    from invars
         cross join lateral jsonb_array_elements(array_b) with ordinality as elb(b1, rn)
)
select a1, b1
  from a_side
       full join b_side
         on a_side.rn = b_side.rn
;

Updated Fiddle.

Hi, Thanks for the answer. I just tried it out and stumbled upon the detail, that this "only works" for arrays of the same length. Do you have a solution that would also work for arrays with different lengths, leaving the entry of the other array zero? — DAndy, Feb 06 '21 at 09:04

Barbaros Özhan · Answer 2 · 2021-02-06T00:28:59.973

You can apply JSONB_ARRAY_ELEMENTS() and JSONB_ARRAY_ELEMENTS_TEXT() functions consecutively including ORDINALITY option

WITH t2 AS
(
SELECT (elm -> 'array_a')::JSONB AS a, (elm -> 'array_b')::JSONB AS b
  FROM t
 CROSS JOIN JSONB_ARRAY_ELEMENTS(jsdata) 
  WITH ORDINALITY AS q(elm, i) 
)
SELECT col1, col2
  FROM t2
  JOIN JSONB_ARRAY_ELEMENTS_TEXT(a) WITH ORDINALITY AS q1(col1, i) ON TRUE 
  JOIN JSONB_ARRAY_ELEMENTS_TEXT(b) WITH ORDINALITY AS q2(col2, j) ON i=j

Demo

How to unnest multiple postgres jsonb arrays in parallel

2 Answers2