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

From Adam Jelinek
Subject 9.3 Json & Array's
Date
Msg-id CAMwTJE5_OORq=40MEd+k_1qMJKe2E=1Y6YBD9Bn7nF+kfuPuRw@mail.gmail.com
Whole thread Raw
Responses Re: 9.3 Json & Array's
List pgsql-hackers
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

pgsql-hackers by date:

Previous
From: Alexander Korotkov
Date:
Subject: Re: GIN improvements part 1: additional information
Next
From: "MauMau"
Date:
Subject: Re: UTF8 national character data type support WIP patch and list of open issues.