On Tue, Aug 7, 2012 at 3:20 AM, Craig Ringer <ringerc@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
