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.
CodePudding user response:
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