Re: JSON in 9.2: limitations

On Tue, Aug 7, 2012 at 3:20 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)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?

merlin