Thread: Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers

Trying to wrap my head around postgresql 9.4 jsonb and would like some help figuring out how to do the following.

Given the following example jsonb:

    ‘{“name1” : value1, “name2”    : value2, “name3” : [int1, int2, int3] }’::jsonb AS table1.column1

Wanted: Return the “name3” array only, as a table with a return signature of

    TABLE( var_name varchar, var_value int, var_row_num int)

So the resulting data would look like this:

    (‘name3’, int1, 1)
    (‘name3’, int2, 2)
    (‘name3’, int3, 3)

Assume the array could be any length except zero and ‘name3’ is guaranteed to exist.

Also posted on stackoverflow:

http://stackoverflow.com/questions/26691725/postgresql-9-4-expand-jsonb-int-array-into-table-with-row-numbers

Thanks,
Neil

Neil Tiffin-3 wrote
> Trying to wrap my head around postgresql 9.4 jsonb and would like some
> help figuring out how to do the following.
>
> Given the following example jsonb:
>
>     ‘{“name1” : value1, “name2”    : value2, “name3” : [int1, int2, int3]
> }’::jsonb AS table1.column1
>
> Wanted: Return the “name3” array only, as a table with a return signature
> of
>
>     TABLE( var_name varchar, var_value int, var_row_num int)
>
> So the resulting data would look like this:
>
>     (‘name3’, int1, 1)
>     (‘name3’, int2, 2)
>     (‘name3’, int3, 3)
>
> Assume the array could be any length except zero and ‘name3’ is guaranteed
> to exist.
>
> Also posted on stackoverflow:
>
> http://stackoverflow.com/questions/26691725/postgresql-9-4-expand-jsonb-int-array-into-table-with-row-numbers

Not syntax checked but...

SELECT 'name3', int_text::integer AS int, int_ord
FROM ( VALUES (...) ) src (column1)
LATERAL ROWS FROM(
json_array_elements(column1->'name3')
) WITH ORDINALITY jae (int_text, int_ord)

Both "WITH ORDINALITY" and "jsonb" are introduced in 9.4; it is possible to
make this work in all supported versions of PostgreSQL through the liberal
use of CTE (WITH) as possibly the generate_series() function.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Help-with-PostgreSQL-9-4-to-expand-jsonb-int-array-into-table-with-row-numbers-tp5825487p5825539.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:

> Neil Tiffin-3 wrote
>> Trying to wrap my head around postgresql 9.4 jsonb and would like some
>> help figuring out how to do the following.
>>
>> Given the following example jsonb:
>>
>>     ‘{“name1” : value1, “name2”    : value2, “name3” : [int1, int2, int3]
>> }’::jsonb AS table1.column1
>>
>> Wanted: Return the “name3” array only, as a table with a return signature
>> of
>>
>>     TABLE( var_name varchar, var_value int, var_row_num int)
>>
>> So the resulting data would look like this:
>>
>>     (‘name3’, int1, 1)
>>     (‘name3’, int2, 2)
>>     (‘name3’, int3, 3)
>>
>> Assume the array could be any length except zero and ‘name3’ is guaranteed
>> to exist.
>>
>> Also posted on stackoverflow:
>>
>> http://stackoverflow.com/questions/26691725/postgresql-9-4-expand-jsonb-int-array-into-table-with-row-numbers
>
> Not syntax checked but...
>
> SELECT 'name3', int_text::integer AS int, int_ord
> FROM ( VALUES (...) ) src (column1)
> LATERAL ROWS FROM(
> json_array_elements(column1->'name3')
> ) WITH ORDINALITY jae (int_text, int_ord)
>
> Both "WITH ORDINALITY" and "jsonb" are introduced in 9.4; it is possible to
> make this work in all supported versions of PostgreSQL through the liberal
> use of CTE (WITH) as possibly the generate_series() function.

I think this can just be written as

SELECT 'name3' AS var_name,
       json_array_elements(column1->'name3') AS var_value,
       row_number() OVER () AS var_row_num
FROM table1