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

Answer 1

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.