Thread: 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.

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

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