From: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | WIP json generation enhancements |
Date: | 2012-11-21 20:16:34 |
Message-ID: | 50AD36A2.30104@dunslane.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
cheers
andrew
Attachment | Content-Type | Size |
---|---|---|
json_enhancements_part1.patch | text/x-patch | 20.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2012-11-21 20:41:44 | Re: [PATCH] binary heap implementation |
Previous Message | Robert Haas | 2012-11-21 20:12:26 | Re: Doc patch: Document names of automatically created constraints and indexes |