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

David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)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