jsonb cross join did not return N*M rows in Postgres

Question

CREATE TABLE mytable (
                         id int,
                         user_id text,
                         changes jsonb,
                         exercise_entry_id int
);

INSERT INTO mytable VALUES
                        (1, 'foo', '["a","b"]', 3),
                        (2, 'foo', '["c","d"]', 3);

Cross join query:

SELECT
        mytable.*,
        elems
    FROM
        mytable  cross  join
        jsonb_array_elements(changes) as elems
    order by mytable.id;

But this query returns only 4 rows as the picture attached. Cross join should return 8 rows. Now only return 4 rows, what point did i miss?

It's a **lateral** cross join so it returns each row from the "base" table as many times as there are elements in the array from that row — , Oct 05 '21 at 10:36

score 1 · Answer 1 · answered Oct 05 '21 at 10:49

It's lateral cross join. I find it on manual.

When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

score 1 · Answer 2 · answered Oct 05 '21 at 10:58

1

If you use a table function in the FROM clause, it is implicitly a LATERAL join, so it will join each row with the function results for that row.

See the documentation:

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

answered Oct 05 '21 at 10:58

Laurenz Albe

209,280
17
206
263

Can you help me on this question (https://stackoverflow.com/questions/69447276/not-understand-json-agg-in-this-context) ? – jian Oct 05 '21 at 11:11

score 0 · Accepted Answer · answered Oct 05 '21 at 10:57

This query returns 8 rows which is the Cartesian product of mytable rows (2) and all elements of all arrays changes (4):

select
    mytable.*,
    elems
from mytable  
cross join (
    select jsonb_array_elements(changes) as elems
    from mytable
    ) s
order by id;

Compare it with the lateral join of a subquery (returns 4 rows):

select
    mytable.*,
    elems
from mytable  
cross join lateral (
    select jsonb_array_elements(changes) as elems
    ) s
order by id;

The subquery may be replaced by a simple function call, in which case the word lateral is optional.

Find the detailed explanation in the documentation.

score 0 · Answer 4 · answered Oct 05 '21 at 11:03

0

Maybe this can help to achieve your needs

with temp as
(
 select jsonb_array_elements( changes)    from mytable
)
select * from mytable m1
cross join temp

The result:

answered Oct 05 '21 at 11:03

Mitko Keckaroski

954
1
8
12

jsonb cross join did not return N*M rows in Postgres

4 Answers4