Re: 9.3 Json & Array's

On 23 September 2013 at 23:37 Adam Jelinek <ajelinek@gmail.com> wrote:

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.

Yeah, I had the same experience.  It is not supported.  I am looking at trying to add support for nested objects and better support for arrays.  Interested in collaborating?

 

 

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[]

^^^ That is what it chokes on.

  ,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

I am still in the process of wrapping my head around the current JSON logic.  I hope to produce a proof of concept that can later be turned into a patch.  See my previous post on this topic.  Again collaboration is welcome.