4 ms·
Only time I've ever really used CROSS LATERAL JOIN in postgres is when working with JSONB documents that I'd like to put into a relational schema, e.g. given th
by rdevilla 5mo ago
Only time I've ever really used CROSS LATERAL JOIN in postgres is when working with JSONB documents that I'd like to put into a relational schema, e.g. given the data
{"data": [
{"id":1,"value":"foo"},
{"id":2,"value":"bar"},
...
]}
the following SQL:
CREATE TABLE my_documents(doc JSONB); SELECT t.id,t.value FROM my_documents CROSS JOIN LATERAL jsonb_to_recordset(doc #> '{data}') AS t(id INTEGER, value TEXT);
... should output the following table:
id | value
----+-------
1 | foo
2 | bar
Useful for manipulating JSON in the database instead of marshalling and unmarshalling everything in the application layer.
IIRC it's really only in a LATERAL JOIN because laterals are the only production rules that let you alias a function call (jsonb_to_recordset()) with explicitly declared column types.
- urams 5mo agoYes, I have a similar only usage but using JSONB_EACH. You can actually replace "CROSS JOIN LATERAL" with a comma which I think is clearer. However, it working as a lateral join is critical as you need the function to fire for every row.
- rdevilla 5mo ago> However, it working as a lateral join is critical as you need the function to fire for every row. Right, what I mean is, for functions in the standard expression position, e.g. `SELECT COALESCE(..., 0)`, those functions will also fire for every row. jsonb_to_recordset however needs to know the schema of the table it will output, and the only way syntactically to declare the column types output by a function returning a recordset is in LATERAL clauses [0]. [0] https://www.postgresql.org/docs/current/sql-select.html https://www.postgresql.org/docs/current/sql-select.html