Clever Geek Handbook

PostgreSQL 9.4 expand jsonb int array into table with row numbers

Trying to wrap my head around postgresql 9.4 jsonb and I'd love to help figure out how to do this.

Considering the following jsonb example:

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

Required: return only the "name3" array as a backsigned table

TABLE( var_name varchar, var_value int, var_row_num int)

Thus, the received data will look like this:

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

Suppose the array can be any length other than zero, and "name3" is guaranteed to exist.

+3


source to share


2 answers


This seems to solve the problem (thanks Bruno), but it seems like more code than needed?

WITH x AS (SELECT 'name3' as aname, jsonb_array_elements(column1->'name3') AS some_value FROM table1)
SELECT x.*, row_number() OVER () FROM x;


Anyone have a better solution?

+1


source


You can use json_array_elements to disable json array

which is obtained fromcolumn1->'name3' 

SELECT 'name3' ,json_array_elements(column1->'name3')
FROM table1;


results

(‘name3’, int1)
(‘name3’, int2)
(‘name3’, int3)
+1


source






More articles:


All Articles