On 11/28/2012 02:08 PM, Merlin Moncure wrote:
On Wed, Nov 28, 2012 at 11:04 AM, Andrew Dunstan <and...@dunslane.net> wrote:
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
Comments (this is awesome btw):
Thanks for the input.
*) ISTM your keytext operators are a reasonable replacement for a
hypothetical json_path. That said you're basically forcing json->sql
mapping through a highly iterative API, which I don't like. At the
very least, I think json_get should return setof json and return all
matching constructions. I won't miss predicate tests: we can do all
that in SQL.
Yes, it's iterative. And for deeply nested json it might be somewhat
inefficient, although the parser is pretty fast AFAICT. But it's a start.
Non-trivial json productions in postgres require the creation of
special composite types that structure the data that we (I?) rig up in
SQL before routing to json. What about having functions that work in
the opposite direction:
*) can you access both arrays and records with numeric positional
syntax (hopefully, yes?), for example:
x->0->0
You can't do that in JS, so I'm not clear why we should allow it.
*) json_object_keys(json) seems to special case to me. how about:
json_each(json) which returns a set of key/value pairs and would on
arrays or objects (for arrays the key could be invented from the
index).
Again, I don't think we should conflate the processing for arrays and
objects. But I could see doing each(json) => setof (text, json) (and
maybe a similar function returning setof (text, text), which would
dequote leaf nodes as json_get_as_text() does).
And similarly a couple of functions to unnest arrays.
*) json_get_as_text(json, keytext or indexint) => text
prefer json_to_text() naming. also json_to_hstore(), etc.
json_to_text seems rather misleading as a name here. Maybe we could
remove the "_as" from the name if that's bothering you.
As for json_to_hstore, as I mentioned, the design is intended to enable
the easy constructyion of such transformations, although for hstores
anything except trivial json structure (i.e. an unnested object) it
might have unappealing results. But in any case, the important thing to
do first is to get the infrastructure in place. Time is very short and I
don't want to extend this very much.
*) have you considered something like
anyelement from_json(anyelement, json)
or
select <json>::some_type; (this may or many not be possible given our
casting mechanics; i don't know).
I have no idea what the semantics of this would be.
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
