Skip site navigation (1) Skip section navigation (2)

Re: JSON for PG 9.2

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Andrew Dunstan <andrew(at)dunslane(dot)net>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: JSON for PG 9.2
Date: 2012-01-31 19:49:41
Message-ID: CAHyXU0wK4kObr3GvhAKhhusGQe1=xic67AQhX6zyZ438wMoVRQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Tue, Jan 31, 2012 at 12:48 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 01/31/2012 01:32 PM, Merlin Moncure wrote:
>> On Tue, Jan 31, 2012 at 12:15 PM, Josh Berkus<josh(at)agliodbs(dot)com>  wrote:
>>>
>>> Andrew,
>>>
>>>> based on Abhijit's feeling and some discussion offline, the consensus
>>>> seems to be to remove query_to_json.
>>>
>>> If we do that, what would getting complete query results back from a
>>> query look like?  It's important to make this as simple for developers
>>> as possible.
>>
>> two options:
>> 1. row_to_json(rowvar)
>> SELECT row_to_json(foo) from foo;
>> SELECT row_to_json(row(a,b,c)) from foo;
>>
>> 2. array_to_json(array_agg()/array())
>> SELECT array_to_json(array(select foo from foo));
>> SELECT array_to_json(array[1,2,3]);
>>
>> #1 I expect will be the more used version -- most json handling client
>> side api (for example node.js drivers) are optimized for row by row
>> processing, but via #2 you can stuff a whole query into single json
>> object if you're so inclined.
>>
>
> You could also write a wrapper something like this:
>
>   create function query_to_json(qtext text) returns json language
>   plpgsql as
>   $$
>   begin
>        return query execute 'select array_to_json(array(' || qtext ||
>   '))';
>   end;
>   $$;

right -- then you can leverage execute/using parameterization etc.
and/or rig a variadic version.

The major hole in functionality I see for heavy json users is the
reverse; how do you get json back into the database?  With xml, at
least you could (ab)use xpath for that...with json you have to rely on
add-on support and/or  ad hoc string parsing (that is, unless I'm
missing something -- I just noted Robert's commit of the JSON type).

since we can do:
select array_to_json(array(select foo from foo));

it seems natural to be able to want do do something like:
WITH foos AS (SELECT a_json_var::foo[] AS f)
(
  INSERT INTO foo SELECT (f).* FROM foos
);

Of course, you'd have to have non-anonymous (that is, defined with
CREATE TYPE AS) types defined to receive all the data, but that's not
so bad.  Also, could xxx_to_json be hypothetically executed via casts?

e.g. select array(select foo from foo)::json;

merlin

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2012-01-31 19:49:52
Subject: Re: Index-only scan performance regression
Previous:From: Robert HaasDate: 2012-01-31 19:47:19
Subject: Re: Progress on fast path sorting, btree index creation time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group