On 11/29/2012 04:52 PM, Merlin Moncure wrote:
On Thu, Nov 29, 2012 at 1:19 PM, Andrew Dunstan <and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
