If we add native JSON syntax to SQL, it is very likely that there will be ambiguities. I think our best path for JSON support is to follow the SQL standard (and perhaps some extensions made by other major DBs), which consists of a set of functions to handle JSON. Quite a few of those functions accept arguments that are JSON paths. For example, here is Oracle's documentation for the JSON_EXISTS function: [1]; Calcite supports JSON_EXISTS too.
You could try creating a variant of Calcite's SQL parser that accepts a wider syntax for expressions and desugars to the standard JSON_xxx functions. If that parser is successful we could perhaps merge it into the main parser, with the extended syntax enabled by setting a flag. Julian [1] https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/condition-JSON_EXISTS.html On Mon, May 3, 2021 at 11:25 AM Amrish Lal <[email protected]> wrote: > > Hello, > > Calcite is used as the parsing layer of a database I am working on. I > noticed that calcite support dot notation and array subscripts in > identifiers as in: > > SELECT json_column.person.name.email[5] FROM table > > This allows for writing rudimentary Json Path expressions. However, some > support is still missing. For example, the following queries will give > parsing errors. > > SELECT json_column.person.name.email[*] FROM table > SELECT json_column.person.name.email[:2] FROM table > SELECT json_column.person.name.email[1,3] FROM table > SELECT json_column..email FROM table > SELECT json_column.person..name FROM table > etc... (more json path examples here > <https://support.smartbear.com/alertsite/docs/monitors/api/endpoint/jsonpath.html> > ) > > From what I can see, this shouldn't be very complicated to add and will > mainly require accepting a wider range of characters in SELECT list > expression values. > > We only need basic json path expression support for now (dot operator and > array subscript operator) which calcite seems to already support, but would > like to add further json path expression support in future. I am wondering > if Calcite is open to further supporting json path expressions in SELECT > and WHERE clause expression list?
