Re: Tackling JsonPath support

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.

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.

- 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
[4] https://msdn.microsoft.com/en-us/library/mt577087.aspx