9.3 Json & Array's

From: Adam Jelinek <ajelinek(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: 9.3 Json & Array's
Date: 2013-09-23 22:37:26
Message-ID: CAMwTJE5_OORq=40MEd+k_1qMJKe2E=1Y6YBD9Bn7nF+kfuPuRw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message MauMau 2013-09-23 22:41:51 Re: UTF8 national character data type support WIP patch and list of open issues.
Previous Message Alexander Korotkov 2013-09-23 21:36:56 Re: GIN improvements part 1: additional information