I am sure I am doing something wrong here, or this is an unsupported feature, but I wanted to double check. I was hoping that if I did a json_agg(x) and then copied that output of that and passed it into a json_populate_recordset that I would get the record back. I know I can make things work using a CTE and other functions like json_each, but I was hoping for a simple one liner.
CREATE SCHEMA varrm; CREATE SEQUENCE varrm.item_id_seq; CREATE TABLE varrm.item (item_id bigint DEFAULT nextval('varrm.item_id_seq') ,title text ,short_desc text ,long_desc text ,tags text[] ,external_api_key text ,trans_timestamp timestamp without time zone DEFAULT now() ,upsert_timestamp timestamp without time zone DEFAULT clock_timestamp() ,end_timestamp timestamp without time zone DEFAULT '9999-12-31 23:59:59.999999'::timestamp without time zone ,CONSTRAINT item_primary_key PRIMARY KEY (item_id) ); INSERT INTO varrm.item (title, short_desc, long_desc, tags, external_api_key) values ('My Title', 'My Short Desc', 'My Very Long Desc', '{GAME, WII, PS4, ACTION, FIRST PERSON SHOOTER}', '1235467'); SELECT json_agg(t1) FROM (SELECT title, short_desc, long_desc, tags, external_api_key FROM varrm.item) AS t1 --output is --[{"title":"My Title","short_desc":"My Short Desc","long_desc":"My Very Long Desc","tags":["GAME","WII","PS4","ACTION","FIRST PERSON SHOOTER"],"external_api_key":null}] SELECT '[{"title":"My Title","short_desc":"My Short Desc","long_desc":"My Very Long Desc","tags":["GAME","WII","PS4","ACTION","FIRST PERSON SHOOTER"],"external_api_key":null}]'::JSON SELECT * FROM json_populate_recordset(null::varrm.item, '[{"title":"My Title","short_desc":"My Short Desc","long_desc":"My Very Long Desc","tags":["GAME","WII","PS4","ACTION","FIRST PERSON SHOOTER"],"external_api_key":null}]'::JSON) /** ERROR: cannot call json_populate_recordset on a nested object ********** Error ********** ERROR: cannot call json_populate_recordset on a nested object SQL state: 22023 */ --Remove the tags part of the json and run again and things work as expected SELECT * FROM json_populate_recordset(null::varrm.item, '[{"title":"My Title","short_desc":"My Short Desc","long_desc":"My Very Long Desc","external_api_key":null}]'::JSON) Thanks Adam Jelinek