2016-11-27 17:50 GMT+01:00 Christian Convey <christian.con...@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": 
> * In MySQL: 
> * In DB2: 
> * In MS SQL Server: 
> * (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
> 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 , , , and . 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",
We can implement subset only - our XPath based on libxml2 does it too. The
good target is support of usual examples on the net.
> - Christian
>  https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
>  https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html
>  http://www.ibm.com/support/knowledgecenter/ssw_
>  https://msdn.microsoft.com/en-us/library/mt577087.aspx