DocumentDB queries with arrays

Question

I have documents with a simple (string-)array property.

{
     "id": "one",
     "tags": ["A", "B"]
}

{
     "id": "two",
     "tags": ["A", "C"]
}

To check, if a value is part of an array, I could use ARRAY_CONTAINS

SELECT * FROM c WHERE ARRAY_CONTAINS(c.tags, "B")

will return document "one".

How could I query for documents with a list of possible values in the array?

Return all Documents where at least one value of the tags array are IN("B", "C").
-> documents "one" and "two"

Andrew Liu · Accepted Answer · 2015-07-09T06:35:11.890

6

You can combine the JOIN operator , which is used to form cross products with nested array elements, with the IN operator.

SELECT docs
FROM docs
JOIN tags IN docs.tags
WHERE tags IN ("B", "C")

Note that because you are creating a cross product, that you will get a result for each matching child element, rather than for each document.

Alternatively you could combine several ARRAY_CONTAINS with OR operators, or write a UDF.

edited Jul 09 '15 at 06:35

answered Jul 07 '15 at 16:00

Andrew Liu

8,045
38
47

Thank you! That almost solved it. No * for Join queries and () instead of [] – Thomas Mutzl Jul 08 '15 at 08:17
Oops - fixed the query :) – Andrew Liu Jul 09 '15 at 06:35
now distinct function is available and you could do with SELECT DISTINCT docs from ... – irriss Dec 03 '18 at 05:29

score 3 · Answer 2 · answered Nov 19 '15 at 21:03

I've managed to reach solution with User Defined Functions suggested by Andrew Liu, because - as he mentioned - this JOIN approach is returning cross product, so every single match is given as a result.

In my case I needed to determine if a user is authorized to see the document - it is reached by group assignment. Each user has list of his groups and each document has a list of groups allowed to view its content.

For described example, imagine that we have a document which has allowed groups ["g1","g2","g3"] and a user which can view documents in groups ["g1","g3"].

With usage of our SQL JOIN method, when we will look for available documents, document described above will be returned twice. Maybe I wouldn't have cared about this if the Distinct function would be available for DocumentDB, but currently it is not (but you can still vote for it).

UDF are defined by using javascript so the code responsible for determining if two lists (arrays) have common element could look like below:

function(array1, array2) {
   return array1.some(function (v) {
       return array2.indexOf(v) >= 0;
   });
}

To define such script from C# code you can follow this snippet:

UserDefinedFunction udf =
            _client.CreateUserDefinedFunctionQuery(_collection.UserDefinedFunctionsLink)
                .Where(x => x.Id == "ArraysHasCommonElem")
                .AsEnumerable()
                .FirstOrDefault();

        if (udf == null)
        {
            udf = new UserDefinedFunction
            {
                Body = @"function(array1, array2) {
                            return array1.some(function (v) {
                                return array2.indexOf(v) >= 0;
                            });
                        }",
                Id = "ArraysHasCommonElem"
            };
            await _client.CreateUserDefinedFunctionAsync(collectionUri.SelfLink, udf);
}

Finally to run working SQL query remeber about 'udf' prefix required before every User Defined Function call:

SELECT * FROM docs 
WHERE udf.ArraysHasCommonElem(your_array1, your_array2)

fyi for anyone tuning in the distinct function is now available in case you don't want logic coupled to a specific collection (udfs live alongside a collection) - https://learn.microsoft.com/en-us/rest/api/cosmos-db/user-defined-functions — Crhistian Ramirez, Jan 25 '19 at 22:30

DocumentDB queries with arrays

2 Answers2

Linked