> > 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.
Thanks, I will try it out and see if it works. Although in my case, we would need to desugar to our own non-standard JSON functions. If that parser is successful we could perhaps merge it into > the main parser, with the extended syntax enabled by setting a flag. That would be great as it would avoid having to manage and maintain a separate branch of Calcite. On Mon, May 3, 2021 at 1:15 PM Julian Hyde <[email protected]> wrote: > 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? >
