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.

Bruno Calza · Accepted Answer · 2014-11-01 19:12:32Z

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)

answered Nov 1, 2014 at 19:12

Bruno Calza

2,8002 gold badges25 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

This works for json, but not for jsonb. It should be SELECT 'name3' , json_array_elements( (column1->'name3')::json) FROM table1;

It also does not get the row numbers of the array.

Neil · Accepted Answer · 2014-11-02 15:19:31Z

1

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?

answered Nov 2, 2014 at 15:11

Neil

5917 silver badges16 bronze badges