Re: WIP json generation enhancements

On 11/21/2012 03:16 PM, Andrew Dunstan wrote:
> Here is a WIP patch for enhancements to json generation.
>
> First, there is the much_requested json_agg, which will aggregate rows 
> directly to json. So the following will now work:
>
>     select json_agg(my_table) from mytable;
>     select json_agg(q) from (<myquery here>) q;
>
> One open question regarding this feature is whether this should return 
> NULL or '[]' for 0 rows. Currently it returns NULL but I could be 
> convinced to return '[]', and the change would be very small.
>
> Next is to_json(), which will turn any value into json, so we're no 
> longer restricted to rows and arrays.
>
> Non-builtin types are now searched for a cast to json, and if it 
> exists it is used instead of the type's text representation. I didn't 
> add a special type to look for a cast to, as was discussed before, as 
> it seemed a bit funky and unnecessary. It can easily be added, but I'm 
> still not convinced it's a good idea. Note that this is only done for 
> types that aren't builtin - we know how to turn all of those into json 
> without needing to look for a cast.
>
> Along with this there is an hstore_to_json() function added to the 
> hstore module, and a cast from hstore to json that uses it. This 
> function treats every value in the hstore as a string. There is also a 
> function with the working title of hstore_to_json_loose() that does a 
> heuristic conversion that treats values of 't' and 'f' as booleans, 
> and strings that look like numbers as numbers unless they start with a 
> leading 0 followed by another digit (could be zip codes, phone numbers 
> etc.) The difference between these is illustrated here (notice that 
> quoted '"t"' becomes unquoted 'true' and quoted '"1"' becomes '1'):
>
>    andrew=# select json_agg(q) from foo q;
>                                 json_agg
> -----------------------------------------------------------------
>      [{"a":"a","b":1,"h":{"c": "t", "d": null, "q": "1", "x": "y"}}]
>    (1 row)
>
>    andrew=# select json_agg(q) from (select a, b, 
> hstore_to_json_loose(h) as h from foo) q;
>                                 json_agg
> ----------------------------------------------------------------
>      [{"a":"a","b":1,"h":{"c": true, "d": null, "q": 1, "x": "y"}}]
>    (1 row)
>
> Note: this patch will need a change in the oids used for the new 
> functions if applied against git tip, as they have been overtaken by 
> time.
>
>
> Comments welcome.
>
>
>

Updated patch that works with git tip and has regression tests.

cheers

andrew