Re: JSON in 9.2: limitations - Mailing list pgsql-general

On Tue, Aug 7, 2012 at 11:31 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Aug 7, 2012 at 3:20 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> (Reposted as the list manager appears to have eaten the first copy):
>>
>> Hey all
>>
>> It seems to be surprisingly hard to build JSON structures with PostgreSQL
>> 9.2's json features, because:
>>
>> - There's no aggregate, function or operator that merges two or more
>> objects; and
>> - there's no single-value "json_escape" or equivalent.
>>
>> Take this example from the SO question
>>
http://stackoverflow.com/questions/11813976/in-postgres-is-there-an-easy-way-to-select-several-attr-val-rows-into-one-recor/11814255#11814255
>>
>> Given:
>>
>> |create  table  t1(  attr textprimary  key,  val text);
>> insert  into  t1values(  'attr1',  'val1'  );
>>
>> insert  into  t1values(  'attr2',  'val3'  );
>>
>> insert  into  t1values(  'attr3',  'val3'  );
>>
>> |
>>
>> Produce:
>>
>> |{ "attr1": "val1",  "attr2" :"val2",  "attr3" : "val3" }
>> |
>>
>>
>> It's very basic, but I couldn't work out a way of doing it that was safe if
>> you also:
>>
>> insert into t1 (attr,val) values ('at"tr', 'v"a"l');
>>
>> which I found quite interesting.
>>
>> With hstore there are several approaches that work:
>>
>> |select  hstore(  array_agg(attr),  array_agg(val)  )  from  t1;
>> |
>>
>> or
>>
>> CREATE AGGREGATE hstore_agg ( basetype = hstore, sfunc = hs_concat, stype =
>> hstore );
>>
>> SELECT hstore_agg( attr => val ) FROM t1;
>> hstore_agg
>> ------------------------------------------------------------------------
>>  "at\"tr"=>"v\"a\"l", "attr1"=>"val1", "attr2"=>"val3", "attr3"=>"val3"
>> (1 row)
>>
>>
>> ... but neither of these appear to be possible with json. Seems like there's
>> a need for a:
>>
>>     json( text[], json[] )
>>
>> and/or:
>>
>>     json_agg( json )
>>
>> to allow the construction of json values. Both of these would also need
>> funcs to create single json literals, a:
>>
>>     json_esc(anyelement) -> json
>>
>> or at least:
>>
>>     json_esc(text) -> json
>>
>>
>> I'm not saying "... some some coding fairy should go and magically create
>> those". I'm interested in opinions. Am I missing something obvious? Is this
>> sort of thing supposed to be done via PL/v8 ? Is it just that the json
>> feature needed to get finished so it was kept small for the first release?
>>
>> Do such functions exist outside the merged patch? If not, would it be
>> helpful to have them written?
>
> why not crosstab the set first then use standard row_to_json?

Well, crosstab is a headache because it requires making an explicit
description of the row fields in the query, which is a  headache if
you don't know the list at the time when the query is made (this is
why I usually wrap crosstab queries with a query generator).

I think this problem could be characterized with general difficulties
in terms of dealing with rowtypes in sql.  The hstore extension is
superior to rowtypes in just about every way (except maybe
performance).  We could really use a hstore_to_json (and maybe
json_to_hstore) feature for the hstore type.   hstores can be
concatenated:

postgres=# select hstore( array_agg(attr), array_agg(val)) ||
hstore('attr4=>val4') from t1;
                              ?column?
--------------------------------------------------------------------
 "attr1"=>"val1", "attr2"=>"val3", "attr3"=>"val3", "attr4"=>"val4"

Given that you can do that, if you had the ability to emit json from
an hstore the OP's problem would be trivially handled.

merlin