Re: 9.3 Json & Array's

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

> 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

On 09/24/2013 12:59 AM, Chris Travers wrote:
>
> 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.
>
>

Feel free to ask questions.

The heart of the API is the event handlers defined in this stuct in
include/utils/jsonapi.h:

typedef struct JsonSemAction
{
void *semstate;
json_struct_action object_start;
json_struct_action object_end;
json_struct_action array_start;
json_struct_action array_end;
json_ofield_action object_field_start;
json_ofield_action object_field_end;
json_aelem_action array_element_start;
json_aelem_action array_element_end;
json_scalar_action scalar;
} JsonSemAction;

Basically there is a handler for the start and end of each non-scalar
structural element in JSON, plus a handler for scalars.

There are several problems that will be posed by processing nested
arrays and objects, including:

* in effect you would need to construct a stack of state that could be
pushed and popped
* JSON arrays aren't a very good match for SQL arrays - they are
unidimensional and heterogenous.

I'm not saying this can't be done - it will just take a bit of effort.

cheers

andrew

> On 24 September 2013 at 13:46 Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> Feel free to ask questions.
>
> The heart of the API is the event handlers defined in this stuct in
> include/utils/jsonapi.h:
>
> typedef struct JsonSemAction
> {
> void *semstate;
> json_struct_action object_start;
> json_struct_action object_end;
> json_struct_action array_start;
> json_struct_action array_end;
> json_ofield_action object_field_start;
> json_ofield_action object_field_end;
> json_aelem_action array_element_start;
> json_aelem_action array_element_end;
> json_scalar_action scalar;
> } JsonSemAction;
>
>
> Basically there is a handler for the start and end of each non-scalar
> structural element in JSON, plus a handler for scalars.
>
> There are several problems that will be posed by processing nested
> arrays and objects, including:
>
> * in effect you would need to construct a stack of state that could be
> pushed and popped

True.

> * JSON arrays aren't a very good match for SQL arrays - they are
> unidimensional and heterogenous.

This is true, but I think one would have to start with an assumption that the
data is valid for an SQL type and then check again once one gets it done.
JSON is a pretty flexible format which makes it a poor match in many cases
for SQL types generally. But I think the example so far (with
json_populate_recordset) is a good one, namely a best effort conversion.

>
>
> I'm not saying this can't be done - it will just take a bit of effort.

Yeah, looking through the code, I think it will be more work than I originally
thought but that just means it will take longer.
>
> cheers
>
> andrew
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
Best Wishes,
Chris Travers
http://www.2ndquadrant.com
PostgreSQL Services, Training, and Support

I agree with the best effort type of conversion, and only being able to
handle JSON array's that conform to an SQL array. With that said I would
love to collaborate with you on this, but there is one thing holding me
back. The current company I work for (an insurance company) says it is a
conflict of interest so I have to be careful. I can try to help out in
other ways if possible, and I will double check with our HR.

On Tue, Sep 24, 2013 at 8:12 AM, Chris Travers <chris(at)2ndquadrant(dot)com>wrote:

> **
>
>
> > On 24 September 2013 at 13:46 Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> >
> >
> > Feel free to ask questions.
> >
> > The heart of the API is the event handlers defined in this stuct in
> > include/utils/jsonapi.h:
> >
> > typedef struct JsonSemAction
> > {
> > void *semstate;
> > json_struct_action object_start;
> > json_struct_action object_end;
> > json_struct_action array_start;
> > json_struct_action array_end;
> > json_ofield_action object_field_start;
> > json_ofield_action object_field_end;
> > json_aelem_action array_element_start;
> > json_aelem_action array_element_end;
> > json_scalar_action scalar;
> > } JsonSemAction;
> >
> >
> > Basically there is a handler for the start and end of each non-scalar
> > structural element in JSON, plus a handler for scalars.
> >
> > There are several problems that will be posed by processing nested
> > arrays and objects, including:
> >
> > * in effect you would need to construct a stack of state that could be
> > pushed and popped
>
> True.
>
> > * JSON arrays aren't a very good match for SQL arrays - they are
> > unidimensional and heterogenous.
>
> This is true, but I think one would have to start with an assumption that
> the data is valid for an SQL type and then check again once one gets it
> done. JSON is a pretty flexible format which makes it a poor match in
> many cases for SQL types generally. But I think the example so far (with
> json_populate_recordset) is a good one, namely a best effort conversion.
>
> >
> >
> > I'm not saying this can't be done - it will just take a bit of effort.
>
> Yeah, looking through the code, I think it will be more work than I
> originally thought but that just means it will take longer.
> >
> > cheers
> >
> > andrew
> >
> >
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> Best Wishes,
> Chris Travers
> http://www.2ndquadrant.com
> PostgreSQL Services, Training, and Support
>

On Tue, Sep 24, 2013 at 3:14 PM, Adam Jelinek <ajelinek(at)gmail(dot)com> wrote:
> I agree with the best effort type of conversion, and only being able to
> handle JSON array's that conform to an SQL array. With that said I would
> love to collaborate with you on this, but there is one thing holding me
> back. The current company I work for (an insurance company) says it is a
> conflict of interest so I have to be careful. I can try to help out in
> other ways if possible, and I will double check with our HR.

pro tip: don't ask until you already did the work.

merlin