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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general