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

From: Neil Tiffin <neilt(at)neiltiffin(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: Help with PostgreSQL 9.4 to expand jsonb int array into table with row numbers
Date: 2014-11-01 18:38:14
Message-ID: 080B2AA5-F5DC-45B3-8379-92F215F3BAB3@neiltiffin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Thanks,
Neil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2014-11-01 20:02:31 Re: Converting char to varchar automatically
Previous Message Anil Menon 2014-11-01 16:37:46 Re: Basic question regarding insert