Tackling JsonPath support

Hi

2016-09-15 18:05 GMT+02:00 Christian Convey <christian.convey@gmail.com>:

On Mon, Sep 5, 2016 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
...

I wrote XMLTABLE function, and I am thinking about JSON_TABLE function.

But

there is one blocker - missing JsonPath support in our JSON

implementation.

So one idea - implement JsonPath support and related JSON query

functions.

This can help with better standard conformance.

Hi Pavel,

Are you still looking for someone to add the JsonPath support to the
JSON implementation? And if so, how urgently are people waiting for
it?

yes - JsonPath support should be great. I hope so this or next commitfest
the XMLTABLE patch will be committed, and with JsonPath I can start to work
on JSON_TABLE function.

But the JsonPath can be merged separately without dependency to JSON_TABLE.
There are more JSON searching functions, and these functions should to
support JsonPath be ANSI SQL compliant.

I'd be happy to start working on a patch, but since I'm new to PG
development, I'm probably not the fastest person to get it done.

It is not problem. Probably you should to do this work without deep
knowledges about PostgreSQL internals. The work with data types (and
functions for data types) is well isolated from PostgreSQL engine.

You can learn from current searching on JSON -
postgresql/src/backend/utils/adt/json.c

And it is good start to be PostgreSQL's hacker - I started with
implementation of own data type and related functions.

Regards

Pavel

Hi Pavel,

Can I check a few assumptions about what you're suggesting for this task?

* 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]http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf.

* When [1]http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf mentions a "JSON path expression" or "JSON path language", it's
referring to the query language described here: [2]http://goessner.net/articles/JsonPath.

* Even if other popular DBMS's deviate from [1]http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf, or other popular JSONPath
implementations deviate from [2]http://goessner.net/articles/JsonPath, we remain committed to a faithful
implementation of [1]http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf.

* It's okay for my first commit to implement just two things: (a) a
PG-internal implementation of JsonPath, and (b) a user-visible
implementation of "JSON_QUERY" based on (a). Later commits could add
implementations of "JSON_VALUE", "JSON_EXISTS", etc. in terms of (a).

Thanks,
Christian

[1]: http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf
http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf

[2]: http://goessner.net/articles/JsonPath

On Fri, Sep 16, 2016 at 2:28 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2016-11-13 18:13 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

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.

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

Hi

2016-11-13 15:14 GMT+01:00 Christian Convey <christian.convey@gmail.com>:

Hi Pavel,

Can I check a few assumptions about what you're suggesting for this task?

* 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].

* When [1] mentions a "JSON path expression" or "JSON path language", it's
referring to the query language described here: [2].

* Even if other popular DBMS's deviate from [1], or other popular JSONPath
implementations deviate from [2], we remain committed to a faithful
implementation of [1].

* It's okay for my first commit to implement just two things: (a) a
PG-internal implementation of JsonPath, and (b) a user-visible
implementation of "JSON_QUERY" based on (a). Later commits could add
implementations of "JSON_VALUE", "JSON_EXISTS", etc. in terms of (a).

My goal is implementation of JSON_TABLE function - this function can be
used instead any other mentioned function (and it is really useful - it is
usual task - transform JSON to table). The SQL/JSON is pretty new and
bigger for implementation in one step. Nobody knows it from PostgreSQL
world. The our SQL/XML needed more than 10 years and still is not fully
complete - and we used power and features libxml2 (nothing similar we have
for JSON). But almost what is daily need from SQL/XML we have. For
JSON_TABLE we need only basic features of JSONPath - the predicates are not
necessary in first step.

http://www.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/json/src/tpc/db2z_bif_jsontable.html
The vendors use name for this query language "SQL/JSON path expressions" -
so important source is SQL/JSON (this can be different than origin JSONPath
(your second source)).

Regards

Pavel

Regards

Pavel

From looking at other databases' docs, it seems like the behavior of
various JSON-related operators / functions are described partially in terms
of a "json path expression":

* In Oracle, "JSON_TABLE", "JSON_exists_column", "JSON_value_column": [1]https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
* In MySQL: [2]https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html
* In DB2: [3]http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzjsonpath.htm
* In MS SQL Server: [4]https://msdn.microsoft.com/en-us/library/mt577087.aspx
* (Whatever the Standards committee will end up producing.)

If I'm correctly understanding the situation, It sounds like we have two
big unknowns:

(a) The exact syntax/semantics of JSON path searching, especially w.r.t.
corner cases and error handling, and

(b) The syntax/semantics of whatever SQL operators / functions are
currently defined in terms of (a). E.g., "JSON_TABLE".

If that's correct, then what do you guys think about us taking the
following incremental approach?

Step 1: I'll dig into the implementations described above, to see what's
similar and different between the JSON-path-expression syntax and semantics
offered by each. I then report my findings here, and we can hopefully
reach a consensus about the syntax/semantics of PG's json-path-expression
handling.

Step 2: I submit a patch for adding a new function to "contrib", which
implements the JSON-path-expression semantics chosen in Step 1. The
function will be named such that people won't confuse it with any
(eventual) SQL-standard equivalent.

Step 3: PG developers can, if they choose, start defining new JSON operator
/ functions, and/or port existing JSON-related functions, in terms of the
function created in Step 2.

I see the following pros / cons to this approach:

Pro: It gives us a concrete start on this functionality, even though we're
not sure what's happening with the SQL standard.

Pro: The risk of painting ourselves into a corner is relatively low,
because we're putting the functionality in "contrib", and avoid function
names which conflict with likely upcoming standards.

Pro: It might permit us to give PG users access to JSONPath -like
functionality sooner than if we wait until we're clear on the ideal
long-term interface.

Con: "JSON path expression" is a recurring them in the *grammars* of
user-facing operators in [1]https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973, [2]https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html, [3]http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzjsonpath.htm, and [4]https://msdn.microsoft.com/en-us/library/mt577087.aspx. But it doesn't
necessarily follow that the function implemented in Step 2 will provide
useful infrastructure for PG's eventual implementations of "JSON_TABLE",
etc.

- Christian

[1]: https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
[2]: https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html
[3]: http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzjsonpath.htm
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzjsonpath.htm
[4]: https://msdn.microsoft.com/en-us/library/mt577087.aspx

2016-11-27 17:50 GMT+01:00 Christian Convey <christian.convey@gmail.com>:

From looking at other databases' docs, it seems like the behavior of
various JSON-related operators / functions are described partially in terms
of a "json path expression":

* In Oracle, "JSON_TABLE", "JSON_exists_column", "JSON_value_column": [1]
* In MySQL: [2]
* In DB2: [3]
* In MS SQL Server: [4]
* (Whatever the Standards committee will end up producing.)

If I'm correctly understanding the situation, It sounds like we have two
big unknowns:

(a) The exact syntax/semantics of JSON path searching, especially w.r.t.
corner cases and error handling, and

(b) The syntax/semantics of whatever SQL operators / functions are
currently defined in terms of (a). E.g., "JSON_TABLE".

If that's correct, then what do you guys think about us taking the
following incremental approach?

Step 1: I'll dig into the implementations described above, to see what's
similar and different between the JSON-path-expression syntax and semantics
offered by each. I then report my findings here, and we can hopefully
reach a consensus about the syntax/semantics of PG's json-path-expression
handling.

Step 2: I submit a patch for adding a new function to "contrib", which
implements the JSON-path-expression semantics chosen in Step 1. The
function will be named such that people won't confuse it with any
(eventual) SQL-standard equivalent.

Step 3: PG developers can, if they choose, start defining new JSON
operator / functions, and/or port existing JSON-related functions, in terms
of the function created in Step 2.

I see the following pros / cons to this approach:

Pro: It gives us a concrete start on this functionality, even though we're
not sure what's happening with the SQL standard.

Pro: The risk of painting ourselves into a corner is relatively low,
because we're putting the functionality in "contrib", and avoid function
names which conflict with likely upcoming standards.

Pro: It might permit us to give PG users access to JSONPath -like
functionality sooner than if we wait until we're clear on the ideal
long-term interface.

Incremental work is great idea - I like this this style. Instead contrib,
you can use public repository on github. Minimally for first stage is
better to live outside core - you are not restricted by PostgreSQL
development process. When your code will be stabilized, then you can go to
commitfest. I believe so we need good JSON support. The XML support helps
to PostgreSQL lot of, JSON will be great too.

Con: "JSON path expression" is a recurring them in the *grammars* of
user-facing operators in [1], [2], [3], and [4]. But it doesn't
necessarily follow that the function implemented in Step 2 will provide
useful infrastructure for PG's eventual implementations of "JSON_TABLE",
etc.

We can implement subset only - our XPath based on libxml2 does it too. The
good target is support of usual examples on the net.

Regards

Pavel

On Sun, Nov 27, 2016 at 11:50:30AM -0500, Christian Convey wrote:

From looking at other databases' docs, it seems like the behavior of

various JSON-related operators / functions are described partially in terms
of a "json path expression":

* In Oracle, "JSON_TABLE", "JSON_exists_column", "JSON_value_column": [1]
* In MySQL: [2]
* In DB2: [3]
* In MS SQL Server: [4]
* (Whatever the Standards committee will end up producing.)

There's another option we should also consider: jq
<https://stedolan.github.io/jq/&gt;. It's available under a
PostgreSQL-compatible license, and has had a LOT of work put into
correctness and performance.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dne 28. 11. 2016 17:26 napsal uživatel "David Fetter" <david@fetter.org>:

On Sun, Nov 27, 2016 at 11:50:30AM -0500, Christian Convey wrote:

From looking at other databases' docs, it seems like the behavior of

various JSON-related operators / functions are described partially in

terms

of a "json path expression":

* In Oracle, "JSON_TABLE", "JSON_exists_column", "JSON_value_column":

[1]:

* In MySQL: [2]
* In DB2: [3]
* In MS SQL Server: [4]
* (Whatever the Standards committee will end up producing.)

There's another option we should also consider: jq
<https://stedolan.github.io/jq/&gt;. It's available under a
PostgreSQL-compatible license, and has had a LOT of work put into
correctness and performance.

we can use it for inspiration. but the syntax of this tool is little bit
too complex and too original against Json path ... jsonpath is relative
simple implementation of xpath to json

we have one proprietary syntax already, two is maybe too much :-)

On Mon, Nov 28, 2016 at 5:20 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
​...​

Con: "JSON path expression" is a recurring them in the *grammars* of

user-facing operators in [1], [2], [3], and [4]. But it doesn't
necessarily follow that the function implemented in Step 2 will provide
useful infrastructure for PG's eventual implementations of "JSON_TABLE",
etc.

We can implement subset only - our XPath based on libxml2 does it too. The
good target is support of usual examples on the net.

​Hi Pavel,

Can you clarify what you meant? I *think* you're saying:

* It's not important for me to match the syntax/semantics of the json-path
implementations found in MySQL / Oracle / DB2 / ​MS SQL Server, and

* Instead, I should just use examples / explanations on the web as my
guidance.

​Thanks,​
Christian

On Mon, Nov 28, 2016 at 5:20 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
...

Incremental work is great idea - I like this this style. Instead contrib,
you can use public repository on github. Minimally for first stage is
better to live outside core - you are not restricted by PostgreSQL
development process. When your code will be stabilized, then you can go to
commitfest. I believe so we need good JSON support. The XML support helps
to PostgreSQL lot of, JSON will be great too.

​Hi Pavel,

Thanks for the suggestion.

I am planning to use one of my own public github repos as the location for
my work.

I thought by adding my first implementation to "contrib", we could make
this functionality available to end-users, even before there was a
consensus about what PG's "official" JSON-related operators should have for
syntax and semantics.

Does my reasoning make sense?

- C

On Mon, Nov 28, 2016 at 9:40 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
​...​

​Hi Pavel,

Can you clarify what you meant? I *think* you're saying:

* It's not important for me to match the syntax/semantics of the

json-path implementations found in MySQL / Oracle / DB2 / ​MS SQL Server,
and

oh no. the syntax is important. But for start we can have a subset. For
json table function .. json to relation mapping is important path
expression. some other features like predicates
are nice, but can be implemented later.

Im sorry. My English is bad.

​Hi Pavel,

You're English is very good, actually. I think the confusion arises from
me speaking in vague terms. I apologize for that. Allow me to be more
specific about what I'm proposing to do.

I propose adding to "contrib" a function with the following characteristics:

* Its signature is "json_path( jsonb from_json, string
json_path_expression) --> jsonb".

* The function will hopefully be a useful building block for PG's
implementation of "official" JSON operators such as "JSON_TABLE". Once the
PG community agrees on what those operators' syntax/semantics should be.
​
* The function will hopefully be immediately useful to PG users who want
JSONPath -like operations on their "jsonb" objects.

- C

On Mon, Nov 28, 2016 at 9:47 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote

I thought by adding my first implementation to "contrib", we could make

this functionality available to end-users, even before there was a
consensus about what PG's "official" JSON-related operators should have for
syntax and semantics.

this time the commiters dislike the contrib dir. It is hard to push there
anything :-(. You can try it, but it can be lost time.

​Thanks for the warning. I'm okay with my patch adding the "json_path"
function to the core PG code.​

I would still suggest that we hold off on having my first patch implement
an official JSON-related operator such as "JSON_TABLE". I would prefer to
have my "json_path" function available to users even before we know how
"JSON_TABLE", etc. should behave.

​Does that sound reasonable?​

I wonder what it might take to integrate jq[1]https://stedolan.github.io/jq (note: jq is NOT JQuery) (via libjq) with
PostgreSQL... The internal representation of JSON data is bound to be
completely different, no doubt, but jq is a fantastic language, akin to
XPath and XSLT combined, but with nice syntax.

[1]: https://stedolan.github.io/jq (note: jq is NOT JQuery)

Nico
--

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 28, 2016 at 05:56:41PM +0100, Pavel Stehule wrote:

Dne 28. 11. 2016 17:26 napsal uživatel "David Fetter" <david@fetter.org>:

There's another option we should also consider: jq
<https://stedolan.github.io/jq/&gt;. It's available under a
PostgreSQL-compatible license, and has had a LOT of work put into
correctness and performance.

we can use it for inspiration. but the syntax of this tool is little bit
too complex and too original against Json path ... jsonpath is relative
simple implementation of xpath to json

we have one proprietary syntax already, two is maybe too much :-)

jq is hardly proprietary :)

JSON Path is not expressive enough (last I looked) and can be mapped
onto jq if need be anyways.

libjq has a number of desirable features, mostly its immutable/COW data
structures. In libjq data structures are only mutated when there's
only one reference to them, but libjq's jv API is built around
immutability, so jv values are always notionally immutable. For
example, one writes:

jv a = jv_array();

a = jv_array_append(a, jv_true()); // `a' is notionally new, but since
// it had only one reference, its
// memory is reused

and similarly for objects. One could instead write:

jv a = jv_array_append(jv_array(), jv_true());

or

jv a = JV_ARRAY(jv_true());

One of the nice things about libjv is that almost every function
consumes a reference of every jv value passed in, with very few
exceptions. This simplifies memory management, or at least avoidance of
double-free and use-after-free (it can be harder to track down leaks
though, since tools like valgrind don't understand that jv_copy() call
sites can be like allocations).

Nico
--

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 28/11/16 18:57, Christian Convey wrote:

On Mon, Nov 28, 2016 at 9:47 AM, Pavel Stehule <pavel.stehule@gmail.com
<mailto:pavel.stehule@gmail.com>> wrote

I thought by adding my first implementation to "contrib", we could make this functionality available to end-users, even before there was a consensus about what PG's "official" JSON-related operators should have for syntax and semantics.

this time the commiters dislike the contrib dir. It is hard to push
there anything :-(. You can try it, but it can be lost time.

​Thanks for the warning. I'm okay with my patch adding the "json_path"
function to the core PG code.​

I would still suggest that we hold off on having my first patch
implement an official JSON-related operator such as "JSON_TABLE". I
would prefer to have my "json_path" function available to users even
before we know how "JSON_TABLE", etc. should behave.

​Does that sound reasonable?​

Hi,

just make it extension, not contrib module, there is not much difference
between those except contrib is included in distribution.

Extensions that provide just functions are easy to integrate into core
(that's how some of the existing json functions were added in the past
as well).

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 28, 2016 at 11:23 AM, Nico Williams <nico@cryptonector.com>
wrote:
...

JSON Path is not expressive enough (last I looked) and can be mapped
onto jq if need be anyways.

​Hi Nico,

Could you please clarify what you mean by "not expressive enough"?

I ask because I've been struggling to identify clear requirements for the
json-path functionality I'm trying to provide. It sounds like perhaps you
have something concrete in mind.

Since I myself have no need currently for this functionality, I'm left
guessing about hypothetical users of it.​ My current mental model is:

(a) Backend web developers. AFAICT, their community has mostly settled on
the syntax/semantics proposed by Stefan Groessner. It would probably be
unkind for PG's implementation to deviate from that without a good reason.

(b) PG hackers who will eventually implement the ISO SQL standard
operators. In the standards-committee meeting notes I've seen, it seemed
to me that they were planning to define some operators in terms of
json-path expression. So it would probably be good if whatever json-path
function I implement turns out to comply with that standard, so that the
PG-hackers can use it as a building block for their work.

(c) Pavel. (I'm still somewhat unclear on what has him interested in this,
and what his specific constraints are.)

- Christian

On Mon, Nov 28, 2016 at 05:50:40PM -0800, Christian Convey wrote:

On Mon, Nov 28, 2016 at 11:23 AM, Nico Williams <nico@cryptonector.com>
wrote:
...

JSON Path is not expressive enough (last I looked) and can be mapped
onto jq if need be anyways.

Hi Nico,

Could you please clarify what you mean by "not expressive enough"?

jq is a functional language that has these and other features:

- recursion
- generators
- lazy evaluation (of sorts)
- path expressions
- math functionality (libm, basically)
- reduction
- functions
- and other things

(jq does not have higher-order functions in that functions cannot return
functions and functions are not values, though it does have closures.)

jq is and feels a lot like a SQL, but for JSON.

I ask because I've been struggling to identify clear requirements for the
json-path functionality I'm trying to provide. It sounds like perhaps you
have something concrete in mind.

SQL imposes structure on data. Recursion makes SQL structure looser in
the sense that it may not be easy or possible to express certain
desirable schema constraints in SQL terms without resorting to triggers,
say. Storing documents in XML, JSON, or other such recursion-friendly
formats (perhaps in semantically equivalent but query-optimized forms)
is also a way to avoid strict structure (thus one needs schema
validators for XML, for example).

Less rigid schema constraints do not and should not preclude powerful
query languages.

One could convert such documents to a SQL EAV schema, if one has an
RDBMS with an ANY type (e.g., something like SQLite3's duck typing), and
then use SQL to query them. But that may be more difficult to use than
a SQL with support for XML/JSON/... and query sub-languages for those.

SQL is very powerful. One might like to have similarly powerful,
format-specific query languages for documents stored in XML, JSON,
etcetera, in a SQL RDBMS. jq is such a language, for JSON documents.
Ditto XPath/XSLT, for XML. While XPath is expressive and compact, XSLT
is rather verbose; jq is as expressive as XSLT, but with the compact
verbosity of XPath.

Since I myself have no need currently for this functionality, I'm left
guessing about hypothetical users of it. My current mental model is:

That's a bit like asking what is the use for SQL :^) The point is that
SQL is a powerful query language, and so is jq. Each is appropriate to
its own domain; both could be used together.

(a) Backend web developers. AFAICT, their community has mostly settled on
the syntax/semantics proposed by Stefan Groessner. It would probably be
unkind for PG's implementation to deviate from that without a good reason.

I can't speak for the community. I wouldn't take it personally that jq
be not chosen, nor any other proposal of mine. If it's politically
easier, then do that.

(b) PG hackers who will eventually implement the ISO SQL standard
operators. In the standards-committee meeting notes I've seen, it seemed
to me that they were planning to define some operators in terms of
json-path expression. So it would probably be good if whatever json-path
function I implement turns out to comply with that standard, so that the
PG-hackers can use it as a building block for their work.

These could still be implemented (e.g., using jq itself).

(c) Pavel. (I'm still somewhat unclear on what has him interested in this,
and what his specific constraints are.)

Hmm?

Nico
--

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 28, 2016 at 6:26 PM, Nico Williams <nico@cryptonector.com>
wrote:

On Mon, Nov 28, 2016 at 05:50:40PM -0800, Christian Convey wrote:

On Mon, Nov 28, 2016 at 11:23 AM, Nico Williams <nico@cryptonector.com>
wrote:
...

JSON Path is not expressive enough (last I looked) and can be mapped
onto jq if need be anyways.

Hi Nico,

Could you please clarify what you mean by "not expressive enough"?

jq is a functional language that has these and other features:

- recursion
- generators
- lazy evaluation (of sorts)
- path expressions
- math functionality (libm, basically)
- reduction
- functions
- and other things

(jq does not have higher-order functions in that functions cannot return
functions and functions are not values, though it does have closures.)

jq is and feels a lot like a SQL, but for JSON.

I ask because I've been struggling to identify clear requirements for the
json-path functionality I'm trying to provide. It sounds like perhaps

you

have something concrete in mind.

SQL imposes structure on data. Recursion makes SQL structure looser in
the sense that it may not be easy or possible to express certain
desirable schema constraints in SQL terms without resorting to triggers,
say. Storing documents in XML, JSON, or other such recursion-friendly
formats (perhaps in semantically equivalent but query-optimized forms)
is also a way to avoid strict structure (thus one needs schema
validators for XML, for example).

Less rigid schema constraints do not and should not preclude powerful
query languages.

One could convert such documents to a SQL EAV schema, if one has an
RDBMS with an ANY type (e.g., something like SQLite3's duck typing), and
then use SQL to query them. But that may be more difficult to use than
a SQL with support for XML/JSON/... and query sub-languages for those.

SQL is very powerful. One might like to have similarly powerful,
format-specific query languages for documents stored in XML, JSON,
etcetera, in a SQL RDBMS. jq is such a language, for JSON documents.
Ditto XPath/XSLT, for XML. While XPath is expressive and compact, XSLT
is rather verbose; jq is as expressive as XSLT, but with the compact
verbosity of XPath.

Since I myself have no need currently for this functionality, I'm left
guessing about hypothetical users of it. My current mental model is:

That's a bit like asking what is the use for SQL :^) The point is that
SQL is a powerful query language, and so is jq. Each is appropriate to
its own domain; both could be used together.

Thanks for the explanation. It sounds like your original point was NOT
that json-path isn't sufficient for "${specific use X}".

Instead, your point was that jq seems to have many advantages over
json-path in general, and therefore PG should offer jq instead or, or in
addition to, json-path.

Is that what you're saying?

​...

(c) Pavel. (I'm still somewhat unclear on what has him interested in

this,

and what his specific constraints are.)

Hmm?

​Context: The reason I'm trying to work on a json-path implementation is
that Pavel Stehule suggested it as a good first PG-hacking project for me.
At the time, it sounded like he had a use for the feature.

- C