CODEWITHSUNDEEP
postgresqljsonblateral-join
mbork
mbork

Reputation: 584

Can a CROSS JOIN be "implicitly LATERAL"?

Assume the following, rather simplistic database:

CREATE TABLE test_table(
  name TEXT,
  data JSONB
);
INSERT INTO test_table VALUES ('name1', '{"a": 1, "b": 2}'), ('name2', '{"c": 3, "d": 4, "e": 5}');

so we have the following table:

# SELECT * FROM test_table ;
 name  |           data
-------+--------------------------
 name1 | {"a": 1, "b": 2}
 name2 | {"c": 3, "d": 4, "e": 5}
(2 rows)

Now I've seen a query like this:

# SELECT * FROM test_table CROSS JOIN JSONB_EACH(test_table.data);

returning the following result:

 name  |           data           | key | value
-------+--------------------------+-----+-------
 name1 | {"a": 1, "b": 2}         | a   | 1
 name1 | {"a": 1, "b": 2}         | b   | 2
 name2 | {"c": 3, "d": 4, "e": 5} | c   | 3
 name2 | {"c": 3, "d": 4, "e": 5} | d   | 4
 name2 | {"c": 3, "d": 4, "e": 5} | e   | 5
(5 rows)

My problem is that I do not understand what happens here. On the one hand, this looks like a LATERAL join, since the right-hand side of JOIN refers to the left-hand one, and the result is perfectly logical. On the other hand, the manual says this:

(Without LATERAL, each sub-SELECT is evaluated independently and so cannot cross-reference any other FROM item.)

and this:

The column source table(s) must be INNER or LEFT joined to the LATERAL item […]

(see here), and of course the CROSS JOIN does not return n×m rows (as this page says).

My question is: does the result of the query above not contradict the manual? If not, does that mean that JSONB_EACH is somehow treated specially? (That I would find surprising.)

Upvotes: 4

Views: 2496

Answers (1)

user330315
user330315

Reputation:

If not, does that mean that JSONB_EACH is somehow treated specially?

Yes it is, because it's a table function (aka "set returning function")

Quote from the manual

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.

(emphasis mine)

Upvotes: 7

Related Questions