I believe that the JSON_EXISTS function [1] can do this kind of filtering, and Calcite supports it [2].
Julian [1] https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html#GUID-8A0043D5-95F8-4918-9126-F86FB0E203F0 <https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html#GUID-8A0043D5-95F8-4918-9126-F86FB0E203F0> [2] https://calcite.apache.org/docs/reference.html#json-functions <https://calcite.apache.org/docs/reference.html#json-functions> > On Oct 17, 2021, at 6:45 AM, Justin Huang <[email protected]> wrote: > > Hi Calcite developers, > > > As we know, MongoDB can store complex JSON objects with deep nested > object/array data. I'd like to know whether calcite SQL parser allow filter > condition with hierarchical element reference like this? > > > select * from doc where key0.key1.key2[0].key3[0] > > 1 > > > Here is an example entry of the collection: > > > { > > "key0": { > > "key1": { > > "key2": [ > > { > > "key3": [ > > 1, > > 2, > > 3 > > ] > > } > > ] > > } > > } > > } > > > > BTW, I see that there are some commercial tools like Studio 3T has good SQL > query capability, not sure if the SQL syntax is their own or some standard > SQL dialect. > > > > > Thanks, > Justin
