Just to add to this, the SQL/JSON proposals I've seen so far, and what Oracle, MSSQL and Teradata chose to implement already is basically subset of jsonpath (some proposals/implementations also include lax/strict prefix keyword on top of that). I think that should give us some hint on what the base functionality should look like.
I agree. My guess is that PG users would benefit most from:
(1) Conformance to whatever ISO standard regarding JSON operators eventually makes it out of the working group.
(2) Compatibility with other widely-used DBMS's.
(3) Compatibility with the JSONPath functionality used by web developers. (Although I don't currently have a grasp on which frameworks / libraries this entails.)
I *think* that (1), (2), and (3) are in approximate agreement about the syntax and semantics of the path-expression language: the language proposed by Stefan Groessner, plus the strict vs. lax distinction.
I think I can satisfy (3) with a PG extension which provides a function that approximately implements JSONPath. My short-term plans are to submit such a patch.
Hopefully that patch's function will be a helpful starting point for satisfying (1) and (2) as well. But that can be decided later.
Nico Williams has argued for using "jq". I don't think jq satisfies any of (1), (2), or (3), so I don't see a good case for incorporating it in my short-term plans. There *may* be a case for using jq internally to my implementation; I'll try to look into that.
