Search code examples
sqlarrayspostgresqlset-returning-functions

Inconsistent results with jsonb_array_elements_text() twice in the SELECT list

Why does the behavior of the query below change when the number of elements in the array changes?

The following snippet expands two arrays on the same query and has two different behaviors:

  • When the two arrays have the same number of elements, one row per element is returned
  • When the two arrays have different number of elements, it behaves like a CROSS JOIN

All of this executed in Postgres 9.5.2:

CREATE TABLE test(a text, b jsonb, c jsonb);

INSERT INTO test VALUES
('A', '["b1","b2"]', '["c1","c2"]'),
('B', '["b1","b2"]', '["c1","c2","c3"]');

SELECT a, jsonb_array_elements_text(b) b, jsonb_array_elements_text(c) c
FROM test;

Here is the result:

A  b1  c1
A  b2  c2
B  b1  c1
B  b2  c2
B  b1  c3
B  b2  c1
B  b1  c2
B  b2  c3

Here is what I would expect:

A  b1  c1
A  b1  c2
A  b2  c1
A  b2  c2
B  b1  c1
B  b2  c2
B  b1  c3
B  b2  c1
B  b1  c2
B  b2  c3
Solution

  • Combining multiple set-returning functions in the SELECT list is not in the SQL standard, where all set-returning elements go into the FROM list. You can do that in Postgres, but it used to exhibit surprising behavior before version 10, where it was finally sanitized.

    All of this is not directly related to the datatype jsonb or the function jsonb_array_elements_text() - beyond it being a set-returning function.

    If you want the Cartesian product, reliably and not depending on your version of Postgres, use CROSS JOIN LATERAL instead (requires at least Postgres 9.3):

    SELECT t.a, jb.b, jc.c
FROM   test t
     , jsonb_array_elements_text(t.b) jb(b)
     , jsonb_array_elements_text(t.c) jc(c)
ORDER  BY t.a, ???;  -- your desired order seems arbitrary beyond a

    The comma in the FROM list (,) is basically short syntax for CROSS JOIN LATERAL here.

    See:

    Explanation for your actual question:

    Why does the behavior of the query below change when the number of elements in the array changes?