Thread: JSON in 9.2: limitations
(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
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? merlin
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
On 08/08/2012 03:45 AM, Merlin Moncure wrote: > 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. That's where my thinking went at first too, but there's a wrinkle with that: json represents the number 1 and the string "1" differently. hstore doesn't. The input data would need to be JSON-escaped before being added to hstore to preserve that difference - so at minimum some kind of scalar json_escape(...) function is still needed. I was also trying to avoid the need to use *two* extensions for the job. -- Craig Ringer
On Tue, Aug 7, 2012 at 7:26 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 08/08/2012 03:45 AM, Merlin Moncure wrote:
>
>> 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.
>
>
> That's where my thinking went at first too, but there's a wrinkle with that:
> json represents the number 1 and the string "1" differently. hstore doesn't.
> The input data would need to be JSON-escaped before being added to hstore to
> preserve that difference - so at minimum some kind of scalar
> json_escape(...) function is still needed.
>
> I was also trying to avoid the need to use *two* extensions for the job.
yeah -- i see your point. that said, in the OP's example, he's
crosstabbing to a tuple which is going to make it a uniform type
anyways. so I'd argue that the problem is that you can't combine
tuples for output to json without discarding a) types or b) field
names:
postgres=# create table foo(a int, b text);
CREATE TABLE
postgres=# create table bar(c int, d text);
CREATE TABLE
via hstore (lose type)
postgres=# select hstore((1,'abc')::foo) || hstore((1,'abc')::bar);
?column?
--------------------------------------------
"a"=>"1", "b"=>"abc", "c"=>"1", "d"=>"abc"
(with hypothetical hstore_to_json, note quoted numerics) :
{"f1":"1","f2":"abc","f3":"2","f4":"def"}
via row() (lose attribute names):
postgres=# select row_to_json(row(((1,'abc')::foo).*, ((2,'def')::bar).*));
row_to_json
---------------------------------------
{"f1":1,"f2":"abc","f3":2,"f4":"def"}
I also get your argument about extension soup, but at the end of the
day I think the right way to go is to get transformation mechanics in
SQL worked out -- that benefits non-json use cases as well. I can't
think of anything better than what you've come up with though.
merlin