Re: [HACKERS] 9.3 Json & Array's
On Tue, Sep 24, 2013 at 3:14 PM, Adam Jelinek 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.3 Json & Array's
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 wrote:
> **
>
>
> > On 24 September 2013 at 13:46 Andrew Dunstan 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@postgresql.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
>
Re: [HACKERS] 9.3 Json & Array's
> On 24 September 2013 at 13:46 Andrew Dunstan 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@postgresql.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
Re: [HACKERS] 9.3 Json & Array's
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.3 Json & Array's
> On 23 September 2013 at 23:37 Adam Jelinek 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_idbigint 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_timestamptimestamp without time zone DEFAULT now()
>,upsert_timestamp timestamp without time zone DEFAULT clock_timestamp()
>,end_timestamp timestamp without time zone DEFAULT '-12-31
> 23:59:59.99'::timestamp without time zone
>,CONSTRAINT item_primary_keyPRIMARY 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
[HACKERS] 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_idbigint DEFAULT nextval('varrm.item_id_seq')
,title text
,short_desc text
,long_desc text
,tags text[]
,external_api_key text
,trans_timestamptimestamp without time zone DEFAULT now()
,upsert_timestamp timestamp without time zone DEFAULT clock_timestamp()
,end_timestamp timestamp without time zone DEFAULT '-12-31
23:59:59.99'::timestamp without time zone
,CONSTRAINT item_primary_keyPRIMARY 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
