Re: json accessors - Mailing list pgsql-hackers

On 11/29/2012 04:52 PM, Merlin Moncure wrote:
> On Thu, Nov 29, 2012 at 1:19 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
>> On 11/29/2012 01:06 PM, Merlin Moncure wrote:
>>> so, just hashing out your proposal and making sure it's reasonable
>>> analogous implementation of xpath.  Sleeping on it, I say mostly, but
>>> not quite. how about some changes for json_get:
>>>
>>> 1) return setof (key, value) in the style of jquery each().
>>> 2) we need some way of indicating in the keytext path that we want to
>>> unnest the collecton pointed to by keytext or to just return it.  for
>>> example,  ->* as indicator?
>>> 3) use double quotes, and make them optional (as hstore)
>>> 4) speaking of hstore, prefer => vs ->?So I don't think your modifications
>>> are well thought out.
>>>
>>>
>>> if you do at least #1 and #2, json_get I think can cover all the bases
>>> for parsing json, meaning you could reproduce the behaviors for each
>>> of your four proposed  just as xpath does for xml.   (you may still
>>> want to add them for posterity or performance though). so no need for
>>> json_each or json_array_unnest etc.
>>
>> json_get is designed to return a single thing. What is more, returning a
>> (key, value) pair seems quite silly when you're passing the key in as an
>> argument. It's not designed to be json_path or json_query, and it's not
>> designed either to take a path expression as an argument. So I don't think
>> this is a good direction. Your proposed mods to json_get modify it out of
>> all recognition. If I offer you a horse and ask what colour you'd like,
>> asking for a lion instead isn't a good response :-)
>>
>> (Repeating myself), I also suggest exposing the transform API so that it
>> will be easy to construct further functions as extensions. I'm not trying to
>> cover the field. The intention here is to provide some very basic json
>> accessors as core functions / operators.
> Right.   But you're not offering a horse to the farm...but to the zoo.
>   json is in core so I don't think you have the luxury of offering a
> clunky API now withe expectation of a sleeker, faster one in the
> future as the old functions will sit around forever in the public
> namespace.  What is present in the API doesn't have to cover all
> reasonable use cases but it certainly should be expected withstand the
> test of time for the cases it does cover.
>
> Sketch out how a object array of indeterminate size would be parsed
> and placed into records with a set returning/array returning and
> non-set returning json_get: which is a better fit?  xpath() doesn't
> work iteratively and nobody has ever complained about that to my
> recollection.
>
> table:  create table foo (a int, b int);
> document: [{"a": 1, "b": 2}, {"a": 3, "b": 4}, ... {"a": 99999, "b": 100000}]
>
> set returning json_get:
> INSERT INTO foo
> SELECT * FROM populate_record(null, hstore_to_json((json_get(*)).value));
>
> assuming '*' is the 'expand this' operator in your 'keytext'
> expression that I was suggestion. How would this work with your
> proposed API?  This is a very typical use case.
>


There are many things wrong with this. First, converting to hstore so 
you can call populate_record is quite horrible and ugly and inefficient. 
And it's dependent on having hstore loaded - you can't have an 
hstore_to_jon in core because hstore itself isn't in core. If you want a 
populate_record that takes data from json we should have one coded 
direct. I'm happy to add it to the list as long as everyone understands 
the limitations. Given a function to unnest the json array, which I 
already suggested upthread, you could do what you suggested above much 
more elegantly and directly. Also, BTW, you can't use * that way.

We are not replicating xpath here for json. Sorry, but that's not my 
goal. If you want to code that up, be my guest.

cheers

andrew