side note: man i hate how people write sql, drives me nuts how wild west it is syntax wise out in the world. i could wax poetic on how its stinky but when it comes to sql, people are already locked in on how they write sql so they are absolutely unwilling to entertain new approaches there. im literally the only one dying on this hill but using caps all over the place when syntax highlighting exists in 2026 is wild. sql might be the one place in the universe where commas on the left in your select statement makes sense, `on` clause join keys deserve to be on the next line and tabbed in under the thing they join to otherwise you're stuffing join tablename alias on alias.id = b.id (this wouldnt be such an eyesore but people are very not-verbose with their table aliases, so eye scanning tablename/alias/join keys kinda sux)
{"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.