How to do an ALL() or ANY() on a documents sub array

Question

Just learning the ins and outs of CosmosDB. I have the following test documents in CosmosDB:

[
    {
        "matchingID":123,
        "many":{
            "meta":"some data",
            "items":[
                {
                    "matchingID":921
                },
                {
                    "matchingID":123
                }
            ]
        }
    },
    {
        "matchingID":1337,
        "many":{
            "meta":"some more data",
            "items":[
                {
                    "matchingID":1337
                },
                {
                    "matchingID":1337
                }
            ]
        }
    },
    {
        "matchingID":9001,
        "many":{
            "meta":"all the datas",
            "items":[
                {
                    "matchingID":42
                },
                {
                    "matchingID":5318008
                }
            ]
        }
    }
]

Please note these are test documents - the actual documents have many more properties in both the root item as well as the sub items. Each item has a subarray under c.many.items. This sub array has a property that may or may not match the one on it's root. What I'm hoping to achieve is to create a query that will allow me to return items where their subitems ALL match, and another query that will return what ANY of them match.

For example SELECT * FROM c WHERE ALL(c.many.items.matchingID) = c.matchingID would return the second item, since all the subitem properties match on their parent:

 {
        "matchingID":1337,
        "many":{
            "meta":"some more data",
            "items":[
                {
                    "matchingID":1337
                },
                {
                    "matchingID":1337
                }
            ]
        }
    }

SELECT * FROM c WHERE ANY(c.many.items.matchingID) = c.matchingID would return the first object, sicne at least one of the sub items matches on the property


    {
        "matchingID":123,
        "many":{
            "meta":"some data",
            "items":[
                {
                    "matchingID":921
                },
                {
                    "matchingID":123
                }
            ]
        }
    }

Steve Zhao Steve Zhao · Accepted Answer · 2020-05-26T03:01:23

I think SELECT * FROM c WHERE ANY(c.many.items.matchingID) = c.matchingID should return the first and the second object.

ANY

try this sql:

SELECT * FROM c where ARRAY_CONTAINS(c.many.items,{"matchingID":c.matchingID},true)

Here is the result:

 [
    {
        "matchingID": 123,
        "many": {
            "meta": "some data",
            "items": [
                {
                    "matchingID": 921
                },
                {
                    "matchingID": 123
                }
            ]
        }
    },
    {
        "matchingID": 1337,
        "many": {
            "meta": "some more data",
            "items": [
                {
                    "matchingID": 1337
                },
                {
                    "matchingID": 1337
                }
            ]
        }
    }
]

ALL

I can't do it just using a sql.So I query twice.

First sql(get the matchingID Array):

select value d.matchingID from (select c.matchingID,max(t.matchingID) as max,min(t.matchingID) as min from c join t in c.many.items group by c.matchingID) d where d.max = d.min and d.max = d.matchingID

By the way, If your matchingID is not unique, please add a unique property after group by,like group by c.matchingID,c.id

Second sql(get the object):

SELECT * FROM c where ARRAY_CONTAINS('first sql result',c.matchingID,true)

Hope these can help you.

Update Answer:

Here is my test udf:

function compareMatchID(matchingID,subArray){
    var result = subArray.every(function(a){
        return a.matchingID == matchingID;
    });
    return result;
}

Here is the sql:

SELECT * FROM c where udf.compareMatchID(c.matchingID,c.many.items)

This sql costs 4.4RUs with your example document.

How to do an ALL() or ANY() on a documents sub array

1 Answers