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"
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With