[rdevilla]

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]

Yes, 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]

> 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