Re: Tackling JsonPath support

Christian Convey <christian.convey@gmail.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.

                        regards, tom lane