In Postgres 9.3, I have a table in which one column contains JSON, as in the test table in the example below.
test=# create table things (id serial PRIMARY KEY, details json, other_field text); CREATE TABLE test=# \d things Table "public.things" Column | Type | Modifiers -------------+---------+----------------------------------------------------- id | integer | not null default nextval('things_id_seq'::regclass) details | json | other_field | text | Indexes: "things_pkey" PRIMARY KEY, btree (id) test=# insert into things (details, other_field) values ('[{"json1": 123, "json2": 456},{"json1": 124, "json2": 457}]', 'nonsense'); INSERT 0 1 test=# insert into things (details, other_field) values ('[{"json1": 234, "json2": 567}]', 'piffle'); INSERT 0 1 test=# select * from things; id | details | other_field ----+-------------------------------------------------------------+------------- 1 | [{"json1": 123, "json2": 456},{"json1": 124, "json2": 457}] | nonsense 2 | [{"json1": 234, "json2": 567}] | piffle (2 rows)
JSON is always an array containing a variable number of hashes. Each hash always has the same set of keys. I am trying to write a query that returns a row for each entry in a JSON array, with columns for each hash key and an identifier from the table of things. I hope the output is as follows:
thing_id | json1 | json2 ----------+-------+------- 1 | 123 | 456 1 | 124 | 457 2 | 234 | 567
i.e. two lines for records with two elements in the JSON array. Is it possible to get Postgres to do this? json_populate_recordset feels like an integral part of the answer, but I can't get it to work with more than one line at once.
source share