Search Postgresql Archives

JSON in 9.2: limitations

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



(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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux