(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?
--
Craig Ringer
