CODEWITHSUNDEEP
arrayspostgresqljsonb
Faust
Faust

Reputation: 15394

Access the index of an element in a jsonb array

I would like to access the index of an element in a jsonb array, like this:

SELECT
  jsonb_array_elements(data->'Steps') AS Step,
  INDEX_OF_STEP

FROM my_process

I don't see any function in the manual for this. Is this somehow possible?

Upvotes: 1

Views: 2606

Answers (2)

klin
klin

Reputation: 121524

Use with ordinality. You have to call the function in the from clause to do this:

with my_process(data) as (
values
    ('{"Steps": ["first", "second"]}'::jsonb)
)

select value as step, ordinality- 1 as index
from my_process
cross join jsonb_array_elements(data->'Steps') with ordinality

   step   | index 
----------+-------
 "first"  |     0
 "second" |     1
(2 rows)

Read in the documentation (7.2.1.4. Table Functions):

If the WITH ORDINALITY clause is specified, an additional column of type bigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1.

Upvotes: 2

nathan hayfield
nathan hayfield

Reputation: 2685

You could try using 

jsonb_each_text(jsonb)

which should supply both the key and value.

There is an example in this question: Extract key, value from json objects in Postgres except you would use the jsonb version.

Upvotes: 1

Related Questions