0

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.

Paul
  • 3,634
  • 1
  • 18
  • 23

1 Answers1

1

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
  • Thank you for the quick answer. I think "technically" it would be idx-1 here, for the query in the above question, for proper array mapping.. – Paul Oct 14 '21 at 21:29