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-SELECTis evaluated independently and so cannot cross-reference any otherFROMitem.)
and this:
The column source table(s) must be
INNERorLEFTjoined to theLATERALitem […]
(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.)