LATERAL Table Functions

Correlated Table Functions with LATERAL

A LATERAL item in FROM may refer to the items before it, so it runs once per row. MySQL 8 524 has lateral derived tables and JSON_TABLE; PostgreSQL 1,289 accepts any set-returning function, which turns one row into many, the ELT way to flatten ETL and ELT's raw JSON orders into lines:

Flattening each order's items array with a lateral table functionSQL

SELECT (o.doc->>'order_id')::int AS order_id, i.book_id, i.qty, i.unit_price
FROM raw.orders_json o
CROSS JOIN LATERAL jsonb_to_recordset(o.doc->'items')
       AS i(book_id int, qty int, unit_price numeric(6,2))
WHERE (o.doc->>'order_id')::int <= 2;

Output

 order_id | book_id | qty | unit_price
----------+---------+-----+------------
        1 |       3 |   1 |      24.00
        1 |       5 |   1 |      16.20
        2 |       3 |   2 |      24.00
        2 |       6 |   2 |      21.30
        2 |       5 |   1 |      16.20

jsonb_to_recordset turns an array of objects into rows typed by the column list after AS; over all orders it yields the same 137,944 lines as JSON, Columnar and Binary Formats's Python. A function in FROM is lateral even without the keyword. CROSS JOIN LATERAL drops orders whose function returns no rows; LEFT JOIN LATERAL ... ON true keeps them.

MENU