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

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?

CodePudding user response：

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).

CodePudding user response：

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