You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Justin Huang <12...@qq.com.INVALID> on 2021/10/17 13:45:22 UTC
[MongoDB Adapter] How to filter collection based on field in deep nested data
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
Re: [MongoDB Adapter] How to filter collection based on field in deep
nested data
Posted by Julian Hyde <jh...@gmail.com>.
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 <12...@qq.com.INVALID> 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