Search code examples
jsonpostgresqljsonbpostgresql-13

Enumerate JSON Array Elements in Postgresql

Is there a way to query jsonb array items with their order enumerated in postgresql?

For example:

SELECT * FROM jsonb_array_elements('[1,2,[3,4]]'::jsonb)

Results:

value
1
2
[3, 4]

I would like these results:

key value
0   1
1   2
2   [3, 4]

Since I think I can safely assume that any select statement not explicitly ordered is unordered, I do not think a simple ROW_NUMBER() would work reliably in this instance.

Solution

  • The operator with ordinality will generate the array index values:

    SELECT idx, value
FROM jsonb_array_elements('[1,2,[3,4]]'::jsonb) with ordinality as t(value, idx)
order by idx