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"

Answer 1

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.

Answer 2

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)