Re: json accessors - Mailing list pgsql-hackers

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
luxuryof 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.

merlin