jsonb_array_elements get element position

Question

I have a table with a JSONB column containing multiple rows. Each row holds a JSON array.

Now I need to put this into rows but I need the position of the elements as well.

e.g.

select *
from (
   select jsonb_array_elements("values"::jsonb) from "myData"
) as x;

Gives me all elements from all arrays of all rows.

How can I get the array index as well?

data                        
----------------------------
{ text: "Hello" } | 0 <-- first array element from first row
{ text: "World" } | 1 <-- second array element from first row
{ text: "Hallo" } | 0 <-- first array element from second row
{ text: "Welt!" } | 1 <-- second array element from second row

Nate Anderson · Accepted Answer · 2024-11-27 14:51:06Z

11

Use a lateral join and with ordinality:

select *
from (
    select t.* 
    from "myData", jsonb_array_elements("values") with ordinality as t(data, idx)
) as x;

If "values" is already a jsonb column there is no need to cast it to jsonb

edited Nov 27, 2024 at 14:51

Nate Anderson

21.7k22 gold badges113 silver badges155 bronze badges

answered Feb 23, 2018 at 8:51

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

dknaack Over a year ago

Great! Do you know how i can add an alias to that? I use that in a subquery and get "subquery in FROM must have an alias"

Collectives™ on Stack Overflow

jsonb_array_elements get element position

How can I get the array index as well?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

How can I get the array index as well?

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related