Thread: 9.3 Json & Array's

9.3 Json & Array's

From
Adam Jelinek
Date:
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

Re: 9.3 Json & Array's

From
Chris Travers
Date:
 
 
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.

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

Re: 9.3 Json & Array's

From
Andrew Dunstan
Date:
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_actionobject_end;        json_struct_action array_start;        json_struct_action array_end;
json_ofield_actionobject_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
goodmatch 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




Re: 9.3 Json & Array's

From
Chris Travers
Date:
 

> On 24 September 2013 at 13:46 Andrew Dunstan <andrew@dunslane.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@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: 9.3 Json & Array's

From
Adam Jelinek
Date:
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@2ndquadrant.com> wrote:
 

> On 24 September 2013 at 13:46 Andrew Dunstan <andrew@dunslane.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@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: 9.3 Json & Array's

From
Merlin Moncure
Date:
On Tue, Sep 24, 2013 at 3:14 PM, Adam Jelinek <ajelinek@gmail.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