This is a proposal to create some basic functions to extract values from
json. The simple functions I envision would be:
* json_object_keys(json) => setof text
returns the set of dequoted, unescaped keys of the object,
errors if it's not an object
* json_get(json, keytext) => json
returns the json value corresponding to the key text in the json object,
null if not found, error if it's not an object
* json_get(json, indexint) => json
returns the json value of the indexth element in the json array,
null of the index is outside the array bounds, errors if it's not an
array
* json_get_as_text(json, keytext or indexint) => text
same as json_get() except that it returns dequoted, unescaped text
for a quoted leaf field
I also propose to map the json_get functions to the operator '->' and
json_get_as_text to '->>', so that given x has this json value:
{"a":[{"b":"c","d":"e"},{"f":true,"g":1}]}
the expression x->'a'->0->>'d' will yield 'e', x->'a'->0->'f' will yield
'true' and x->'a'->0 will yield '{"b":"c","d":"e"}'. The operators would
make using these a whole lot nicer :-)
Various people have suggested putting json_path or something similar
into the core. I'm not sure we want to do that, partly because there are
several competing entries in this field, and partly because I don't want
to get into the business of evaluating json predicate tests, which I
think any tolerably complete gadget would need to do.
Regarding implementation, the way I propose to do this is to modify the
json parser a bit to turn it into a recursive descent parser, with hooks
for various operations. NULL hooks would leave us with the validating
parser we have now with no side effects. The hook functions themselves
will be very small. This would also allow us to do other things very
simply at a later stage, for example a json to xml transformation
function would be very easy to construct using this infrastructure, and
without disturbing any existing functionality.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
