WITH ORDINALITY

WITH ORDINALITY

WITH ORDINALITY and Set-Returning Functions

SQL rows have no position unless you keep one. WITH ORDINALITY appends a bigint counter, from 1, to any function in FROM, recovering each item's array position, the line_no that to_csv.py computed in Python:

Positions of JSON array items with WITH ORDINALITYSQL

SELECT (o.doc->>'order_id')::int AS order_id, i.n AS line_no, i.item->>'book_id' AS book_id
FROM raw.orders_json o
CROSS JOIN LATERAL jsonb_array_elements(o.doc->'items') WITH ORDINALITY AS i(item, n)
WHERE (o.doc->>'order_id')::int = 2;

Output

 order_id | line_no | book_id
----------+---------+---------
        2 |       1 | 3
        2 |       2 | 6
        2 |       3 | 5

row_number() OVER () is no substitute: it numbers rows in whatever order the plan produces. unnest(a, b) zips several arrays side by side, and string_to_table, regexp_split_to_table and jsonb_each split text and objects; all accept WITH ORDINALITY.

MENU