Are we interested in SQL/JSON Path Expressions?

[cburgmer]

Looks like SQL has a quasi? standard for something similar to JSONPath. Is it similar enough that we should care?

• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/json-path-expressions.html#GUID-2DC05D71-3D62-4A14-855F-76E054032494
• https://docs.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server?view=sql-server-ver15
• https://www.postgresql.org/docs/12/functions-json.html

[cburgmer]

See #10 for a WIP implementation against Postgres' implementation.

[keywan-ghadami]

I am very interested in that standard because it adds useful things on top of jsonpath making it a expression language that allows arithmetic operations, using variables, date conversion and more.

I found it available to download (copy right protect but free to read) https://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip

After the first standard of jsonPath it could be possible to add some of the futures step by step or use this ideas for standards build on top of JSONPath something like a "Json Path Expression Language".

We could also already start collecting ideas like making brakets optional for filtering expressions for future versions of the standard.

[danielaparker]

I agree. It seems to me that for "legacy JSONPath", the only standard that matters is the emerging SQL/JSON standard, that is what Google, Amazon, Microsoft and Oracle appear to be coalescing around. It would be interesting to have some columns in the comparison table that show what they produce, also covering functions. "New JSONPath" that innovates will likely want to be compatible with that.

[nichtich]

SQL/JSON path language has been standardized via ISO in 2016 and a new revision in 2021 (ISO/IEC 19075-6:2021, section 7). The syntax shares some parts with JSONPath and its is obviously inspired by it, but it is an independent query language. Here is a quote from a draft from 2014 (the current standard is not publicly available) to give some context:

We began by reviewing available query languages for JSON. We found the following:

• JPath
• JSONPath
• JAQL
• JSONiq
• Mongo

Although we found interesting ideas in each of these, we do not believe that any of them has emerged as a “winner” in the marketplace. Consequently, we decided to define our own place-holder query language, which is called the SQL/JSON path language in the proposal.

From a technical point of view there is no need to include SQL/JSON Path in this comparison, but from a practical point of view it is very relevant because

• both language are likely to be confused
• there is a large overlap between both
• people will be interested which features can be used in both languages and/or how to translate between both languages

I think the issue could best be addressed by

• [ ] add a short section "What is JSONPath?" to the FAQ (this is useful anyway), mentioning SQL/JSON Path
• [ ] add "not to be confused with SQL/JSON Path" to the introduction line at the top of the home page
• [ ] create a comparison between both languages and link it from home page and FAQ

[cburgmer]

Thanks for the context.

If you like to sponsor (even if it's just part of the changes), feel free to have at it!

[relu91]

I stumbled upon this issue while I was working on a service compliant with the Web of Things Thing Description Directory specification. As you can see the service exposes a search endpoint that accepts JSONPath.

Not sure if it might be helpful to somebody else but we ended up creating a simple library that translates the current IETF working draft to SQL/JSONPath. Sadly, it is a lossy conversion but it works quite well in our use cases. Currently, the known limitations are listed in the README but there are probably more when considering the Descendant Selector and AbsolutePaths in filters.

I want to thank @nichtich for https://github.com/cburgmer/json-path-comparison/issues/61#issuecomment-1014271912 it was an eye opener 😄 and I agree that some text would be helpful for newcomers.