Re: jsonpath

On Mon, Jan 21, 2019 at 6:05 PM Oleg Bartunov <obartunov(at)postgrespro(dot)ru> wrote:
> On Mon, Jan 21, 2019 at 1:40 AM Alexander Korotkov
> <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> >
> > On Sun, Jan 20, 2019 at 6:30 AM Alexander Korotkov
> > <a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> > > I'll continue revising this patchset. Nikita, could you please write
> > > tests for 3-argument versions of functions? Also, please answer the
> > > question regarding "id" property.
> >
> > I've some more notes regarding function set provided in jsonpath patch:
> > 1) Function names don't follow the convention we have. All our
> > functions dealing with jsonb have "jsonb_" prefix. Exclusions have
> > "jsonb" in other part of name, for example, "to_jsonb(anyelement)".
> > We could name functions at SQL level in the same way they are named in
> > C. So, they would be jsonb_jsonpath_exists() etc. But it's probably
> > too long. What about jsonb_path_exists() etc?
>
> jsonpath_exists is similar to xpath_exists.

That's true. The question is whether it's more important to follow
json[b] naming convention or xml/xpath naming convention? I guess
json[b] naming convention is more important in our case.

> Actually, we could use jsonb_exists(jsonb, jsonpath, json), but then
> we should specify the type of the second argument.

Yes, but AFAICS the key point of json[b]_ prefixes is to evade
function overloading. So, I'm -1 for use jsonb_ prefix and have
function overload because of that.

> > 2) jsonpath_query_wrapped() name seems counter intuitive for me. What
> > about jsonpath_query_array()?
>
> The question is should we try to provide a functional interface for
> all options of
> JSON_QUERY clause ? The same is for other SQL/JSON clauses.
> Currently, patch contains very limited subset of JSON_QUERY
> functionality, mostly for jsonpath testing.

Actually, my point is following. We have jsonpath patch close to the
committable shape. And we have patch for SQL/JSON clauses including
JSON_QUERY, which is huge, complex and didn't receive any serious
review yet. So, we'll did our best on that patch during this release
cycle, but I can't guarantee it will get in PostgreSQL 12. Thus, my
idea is to make jsonpath patch self contained by providing brief and
convenient procedural interface. This procedural interface is anyway
not a part of standard. It *might* be inspired by standard clauses,
but might be not. I think we should try to make this procedural
interface as good and convenient by itself. It's our extension, and
it wouldn't make us more or less standard conforming.

> > 3) jsonpath_query_wrapped() behavior looks tricky to me. It returns
> > NULL for no results. When result item is one, it is returned "as is".
> > When there are two or more result items, they are wrapped into array.
> > This representation of result seems extremely inconvenient for me. It
> > becomes hard to solve even trivial tasks with that: for instance,
> > iterate all items found. Without extra assumptions on what is going
> > to be returned it's also impossible for caller to distinguish single
> > array item found from multiple items found wrapped into array. And
> > that seems very bad. I know this behavior is inspired by SQL/JSON
> > standard. But since these functions are anyway our extension, we can
> > implement them as we like. So, I propose to make this function always
> > return array of items regardless on count of those items (even for 1
> > or 0 items).
>
> Fair enough, but if we agree, that we provide an exact functionality of
> SQL clauses, then better to follow the standard to avoid problems.

No, I see this as our extension. And I don't see problems in being
different from standard clauses, because it's different anyway. For
me, in this case it's better to evade problems of users. And current
behavior of this function seems like just single big pain :)

> > 4) If we change behavior of jsonpath_query_wrapped() as above, we can
> > also implement jsonpath_query_one() function, which would return first
> > result item or NULL for no items.
> > Any thoughts?
>
> I think, that we should postpone this functional interface, which could be
> added later.

The reason we typically postpone things is that they are hard to bring
to committable shape. jsonpath_query_one() doesn't cost us any real
development. So, I don't see point in postponing that if consider
that as good part of procedural jsonpath interface.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company