CODEWITHSUNDEEP
postgresqljsonbcross-join
jian
jian

Reputation: 4877

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

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? crossjoin

Upvotes: 1

Views: 91

Answers (4)

Mitko Keckaroski
Mitko Keckaroski

Reputation: 1040

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: enter image description here

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 247625

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.

Upvotes: 1

klin
klin

Reputation: 121834

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.

Upvotes: 0

jian
jian

Reputation: 4877

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

Upvotes: 1

Related Questions