CODEWITHSUNDEEP
azure-cosmosdb
Herb Stahl
Herb Stahl

Reputation: 439

DocumentDB SQL where Array of Objects matches an array of strings

reference: DocumentDB SQL with ARRAY_CONTAINS

Question: Is there a better and more efficient query for what is happening below?

In the above reference question, the UDF was written to check if an object in the array had a match to the passed in string. In this variant, I am passing into the UDF an array of strings.

Now I have a working O(N^2) version that I would hope CosmosDB had a more efficient solution for.

function ScopesContainsNames(scopes, names){
    var s, _i,_j, _ilen, _jLen;
    for (_i = 0, _ilen = scopes.length; _i < _ilen; _i++) {
        for (_j = 0, _jLen = names.length; _j < _jLen; _j++) {
            s = scopes[_i];
            n = names[_j];
            if (s.name === n) {
                return true;
            }
        }
    }
    return false;
}

My QUERY looks like this.

SELECT * FROM c WHERE udf.ScopesContainsNames(c.scopes, ["apples", "strawberries", "bananas"])

The following is an example of my Document:

{
    "scopes": [
        {
            "name": "apples",
            "displayName": "3048b61e-06d8-4dbf-a4ab-d4c2ba0a8943/a"
        },
        {
            "name": "bananas",
            "displayName": "3048b61e-06d8-4dbf-a4ab-d4c2ba0a8943/a"
        }
    ],
    "enabled": true,
    "name": "dc1e4c12-95c1-4b7f-bf27-f60f0c29bf52/a",
    "displayName": "218aea3d-4492-447e-93be-2d3646802ac6/a",
    "description": "4aa62367-7421-4fb6-88c7-2699c9c309dd/a",
    "userClaims": [
        "98988d5b-38b5-400c-aecf-da57d2b66433/a"
    ],
    "properties": {
        "437d7bab-a4fb-4b1d-b0b9-f5111d01882a/a": "863defc1-c177-4ba5-b699-15f4fee78ea5/a"
    },
    "id": "677d4a49-a46c-4613-b3f6-f390ab0d013a",
    "_rid": "q6I9AOf180hJAAAAAAAAAA==",
    "_self": "dbs/q6I9AA==/colls/q6I9AOf180g=/docs/q6I9AOf180hJAAAAAAAAAA==/",
    "_etag": "\"00000000-0000-0000-1ede-f2bc622201d5\"",
    "_attachments": "attachments/",
    "_ts": 1560097098
}

Upvotes: 1

Views: 73

Answers (1)

Jay Gong
Jay Gong

Reputation: 23792

If i don't misunderstanding your requirement,you need to search the results where any name property of scopes array is included by the ["apples", "strawberries", "bananas"].

No need to use udf, please see the sample documents i made as below:

enter image description here

Using sql:

SELECT distinct c.scopes FROM c
join fruit in c.scopes
where Array_contains(["apples", "strawberries", "bananas"],fruit.name,false)

Result:

enter image description here

Upvotes: 1

Related Questions