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)