On Tue, Aug 7, 2012 at 11:31 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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? 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
