Re: json accessors

On 11/28/2012 02:08 PM, Merlin Moncure wrote:
> On Wed, Nov 28, 2012 at 11:04 AM, Andrew Dunstan <andrew(at)dunslane(dot)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