Postgres: expand JSON array elements into values with their index?

Question

I have a table with a column containing a JSON array. As an example:

with example as (
    select '["a", "b"]'::jsonb as col
    union select ('["c", "d", "e"]'::jsonb) as col
)
select col from example

Returns:

col
["a", "b"]
["c", "d", "e"]

I can use jsonb_array_elements to expand out each array into rows:

select jsonb_array_elements(col) from example

Returns:

jsonb_array_elements
"a"
"b"
"c"
"d"
"e"

I want the index of each array element along with the element itself (a bit like Python's enumerate), like so:

jsonb_array_elements    array_index
"a"                     1
"b"                     2
"c"                     1
"d"                     2
"e"                     3

How can I do this?

My application has read-only access, so I cannot create functions.

user330315 · Accepted Answer · 2018-02-16 10:19:17Z

7

Use with ordinality:

with example (col) as (
  values 
    ('["a", "b"]'::jsonb),  
    ('["c", "d", "e"]'::jsonb)
)
select t.*
from example, jsonb_array_elements(col) with ordinality as t(e,idx)

returns:

e   | idx
----+----
"a" |   1
"b" |   2
"c" |   1
"d" |   2
"e" |   3

with ordinality can only be used if you use the set returning function in the from clause, which is highly recommended anyway.

answered Feb 16, 2018 at 10:19

user330315

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

Comments

vancouverwill · Accepted Answer · 2018-02-16 10:15:30Z

1

Ahh that's an interesting little postgres puzzle. How about the below?

   WITH example1 AS (
        SELECT '["a", "b"]'::jsonb AS col

    ),
    example2 AS (
        SELECT ('["c", "d", "e"]'::jsonb) AS col
    )

    SELECT  1 AS group, jsonb_array_elements(col) AS jcol, row_number() OVER (ORDER BY jsonb_array_elements(col)) FROM example1
    UNION
    SELECT 2 AS group, jsonb_array_elements(col) AS jcol, row_number() OVER (ORDER BY jsonb_array_elements(col)) FROM example2
    ORDER BY "group", row_number ASC;

That will give you

1   "a" 1
1   "b" 2
2   "c" 1
2   "d" 2
2   "e" 3

answered Feb 16, 2018 at 10:15

vancouverwill

1271 silver badge11 bronze badges

Collectives™ on Stack Overflow

Postgres: expand JSON array elements into values with their index?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related