PostgreSQL json_array_elements in FROM clause - why is this not cartesian join?

Question

If I have an expression like

SELECT t.json_column->>'x',
   nested->>'y'
FROM my_table t,
   json_array_elements(t->'nested') nested

Why don't I need a JOIN? More precisely, why does this not act like a Cartesian CROSS JOIN?

It looks like a join happens implicitly by referencing the table alias t in the json_array_elements call. But the implicit-join syntax with a table function is unfamiliar to me.

Are there other examples of similar SQL syntax in PostgreSQL or other databases?

Answer 1

In fact this is old-fashioned syntax for CROSS JOIN. Formal equivalent:

SELECT
    t.json_column->>'x',
    nested->>'y'
FROM 
    my_table t
CROSS JOIN
    json_array_elements(t.json_column->'nested') nested;

The query does not produce cartesian product but acts rather like an inner join. This is because it has a hidden reference between two parts of join, in this case alias t. This kind of join is known as LATERAL JOIN. For the documentation:

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

If one of parts of a join is a function it is treated as lateral by default.