From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers |
Date: | 2014-11-04 04:15:15 |
Message-ID: | 1415074515830-5825539.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | hari.fuchs | 2014-11-04 08:32:16 | Re: Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers |
Previous Message | Andreas Kretschmer | 2014-11-04 03:13:50 | Re: Is it possible to set a timeout for optimization in PostgreSQL? |