Search code examples
sqljsonpostgresql

jsonb_array_elements get element position

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
Solution

  • 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