Re: Tackling JsonPath support

2016-11-13 18:13 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Christian Convey <christian(dot)convey(at)gmail(dot)com> writes:
> > * Our ultimate goal is to give Postgres an implementation of the
> functions
> > "JSON_EXISTS", "JSON_VALUE", and "JSON_QUERY" which fully comply with the
> > SQL standards.
> > * The best representation of those standards is found here: [1].
> > [1]
> > http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-
> Opening-Plenary.pdf
>
> You're going to need to find a draft standard somewhere, as that
> presentation is too thin on details to support writing an actual
> implementation. In particular, it's far from clear that this is
> true at all:
>
> > * When [1] mentions a "JSON path expression" or "JSON path language",
> it's
> > referring to the query language described here: [2].
> > [2] http://goessner.net/articles/JsonPath
>
> The one slide they have on the path language mentions a lax/strict syntax
> that I don't see either in the document you mention or in the Wikipedia
> XPath article it links to. This does not give me a warm feeling. The SQL
> committee is *fully* capable of inventing their own random path notation,
> especially when there's no ISO-blessed precedent to bind them.
>
> In general, the stuff I see in these WG3 slides strikes me as pretty
> horribly designed. The committee is evidently still stuck on the idea
> that every feature they invent should have a bunch of new bespoke syntax
> for function calls, which is a direction we really don't want to go in
> because of the parser overhead and need for more fully-reserved keywords.
> For instance:
> WHERE JSON_EXISTS (T.J, 'strict $.where' FALSE ON ERROR)
> Really? Who thought that was a better idea than a simple bool parameter?
>
> I have no objection to providing some functions that implement XPath-like
> tests for JSON, but I'm not sure that you ought to try to tie it to
> whatever the SQL committee is going to do, especially when they've not
> published a finished standard yet. You may be chasing a moving target.
>
> As for whether JSONPath is the right spec to follow, I'm not sure.
> The article you mention is from 2007 and I don't see all that many
> other references in a Google search. I found this Wikipedia page:
> https://en.wikipedia.org/wiki/Comparison_of_data_serialization_formats
> which mentions half a dozen competitors, including "JSON Pointer"
> which has at least gotten as far as being an RFC standard:
> https://tools.ietf.org/html/rfc6901
> I'm not up enough on the JSON ecosystem to know which of these has the
> most traction, but I'm unconvinced that it's JSONPath.
>

We can use some other databases with this implementation as references.

I have to agree, so the people in SQL committee are not too consistent -
and sometimes creates too cobolish syntax, but it is standard - and it is
implemented by major vendors.

We doesn't need to implement full API - not in first step - important point
is don't close door to possible ANSI conformance. In first step we can take
the best and important from standard. It can be similar to our SQL/XML
implementation - we implement maybe 75% - and only XPath instead XQuery,
but I don't feel any weak. I see very useful "JSON_TABLE" function, which
is good for start.

Regards

Pavel

> regards, tom lane
>