Re: 9.3 Json & Array's - Mailing list pgsql-hackers

From Chris Travers
Subject Re: 9.3 Json & Array's
Date
Msg-id 1232572560.218413.1379998773414.open-xchange@email.1and1.co.uk
Whole thread Raw
In response to 9.3 Json & Array's  (Adam Jelinek <ajelinek@gmail.com>)
Responses Re: 9.3 Json & Array's
List pgsql-hackers
 
 
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

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: [RFC] Extend namespace of valid guc names
Next
From: Ronan Dunklau
Date:
Subject: Re: Extensions makefiles - coverage