Re: json accessors

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Hannu Krosing <hannu(at)krosing(dot)net>, Hannu Krosing <hannu(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: json accessors
Date: 2012-11-29 22:14:14
Message-ID: 50B7DE36.6080406@dunslane.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


On 11/29/2012 04:52 PM, Merlin Moncure wrote:
> On Thu, Nov 29, 2012 at 1:19 PM, Andrew Dunstan <andrew(at)dunslane(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-11-29 22:20:25 Re: Overlength socket paths (was Re: [COMMITTERS] pgsql: Refactor flex and bison make rules)
Previous Message Merlin Moncure 2012-11-29 21:52:11 Re: json accessors