Re: 9.3 Json & Array's

> On 23 September 2013 at 23:37 Adam Jelinek <ajelinek(at)gmail(dot)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.

Best Wishes,
Chris Travers
http://www.2ndquadrant.com
PostgreSQL Services, Training, and Support