Cosmos DB filter external array using collection

Question

I want to find which items in my array are NOT in the collection. I can filter a collection based on an array, no problem. But, can I filter an array based on the collection in a single query?

This document talks about constructing a join to 'external reference data' so you can include this data in your query:

https://learn.microsoft.com/en-gb/azure/cosmos-db/nosql/query/subquery#mimic-join-with-external-reference-data

I can't figure out if its possible to select from an array, like the units table referenced in the link, and then filter this to NOT include items from the collection? This type of query doesn't work:

SELECT * from (select value ["valueA", "valueB"]) as x 
JOIN y in collectionName 
WHERE x not array_contains(x, y.property)

It seems I HAVE to select from the collection first as in: select [whatever] from COLLECTION. I can't select from an array THEN join the collection like: select [whatever] from ARRAY() join COLLECTION?

Do I have to pull the ENTIRE collection (restructured docs, just the data I want) then filter the array in code?

Answer 1

Input:

[{
"id": "1",
"VariantNo": "1",
"brand": "XXXX",
"Consumer": "XXX_V2",
        "Assignments": [
{
            "Fruit": "Grape",
            "Owner": "Jason"
        }]  
},{
            "id": "2",
"VariantNo": "2",
"brand": "XXX",
"Consumer": "XXXX_V2",
        "Assignments": [
        {
                 "Fruit": "Grape",
            "Owner": "Peter"
             }]
    },{
    "id": "3",
    "VariantNo": "2",
    "brand": "XXXX",
    "Consumer": "XXX_V2",
    "Assignments": [
           {
            "Fruit": "Grape"
           }]
},{
            "id": "4",
"VariantNo": "1",
"brand": "XXXX",
"Consumer": "XXX_V2"
},{
    "id": "5",
    "VariantNo": "3",
    "brand": "XXXX",
    "Consumer": "XXX_V2"
}]

When we try to perform join of collection with array, Cosmos DB doesn’t support it. As Cosmos DB currently only support joins on single document of a collection. Please refer below screenshot, when we try to join array with collection, and we got error the identifier document (which is collection name) couldn’t resolve.

Alternate to the above, below query will return the result where Assignment Array of collection contains property “Owner” and Value of Owner not equal to “Ritika”.

Query

SELECT c.id,c.Assignments FROM c
join x in c.Assignments
where  EXISTS (SELECT value t from t in 
c.Assignments where t.Owner not in('Ritika') or
not is_defined(t.Owner))