PostgreSQL 9.4 expand jsonb int array into table with row numbers

Question

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.

Answer 1

You can use json_array_elements to unnest the json array that results from column1->'name3'

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

results

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

Answer 2

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

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?