DocumentDB filter an array by an array

Question

I have a document that looks essentially like this:

{
     "Name": "John Smith",
     "Value": "SomethingIneed",
     "Tags: ["Tag1" ,"Tag2", "Tag3"]
 }

My goal is to write a query where I find all documents in my database whose Tag property contains all of the tags in a filter.

For example, in the case above, my query might be ["Tag1", "Tag3"]. I want all documents whose tags collection contains Tag1 AND Tag3.

I have done the following:

tried an All Contains type linq query

    var tags = new List<string>() {"Test", "TestAccount"};

    var req =
        Client.CreateDocumentQuery<Contact>(UriFactory.CreateDocumentCollectionUri("db", "collection"))
        .Where(x => x.Tags.All(y => tags.Contains(y)))
        .ToList();

Created a user defined function (I couldn't get this to work at all)

var tagString = "'Test', 'TestAccount'";

    var req =
        Client.CreateDocumentQuery<Contact>(UriFactory.CreateDocumentCollectionUri("db", "collection"),
            $"Select c.Name, c.Email, c.id from c WHERE udf.containsAll([${tagString}] , c.Tags)").ToList();

with containsAll defined as:

function arrayContainsAnotherArray(needle, haystack){
   for(var i = 0; i < needle.length; i++){
     if(haystack.indexOf(needle[i]) === -1)
        return false;
   }
   return true;
 }

Use System.Linq.Dynamic to create a predicate from a string

 var query = new StringBuilder("ItemType = \"MyType\"");

    if (search.CollectionValues.Any())
    {
        foreach (var searchCollectionValue in search.CollectionValues)
        {
            query.Append($" and Collection.Contains(\"{searchCollectionValue}\")");
        }
    }

3 actually worked for me, but the query was very expensive (more than 2000 RUs on a collection of 10K documents) and I am getting throttled like crazy. My result set for the first iteration of my application must be able to support 10K results in the result set. How can I best query for a large number of results with an array of filters?

Thanks.

Larry Maccherone Larry Maccherone · Accepted Answer · 2017-03-08T18:30:58

The UDF could be made to work but it would be a full table scan and so not recommended unless combined with other highly-selective criteria.

I believe the most performant (index-using) approach would be to split it into a series of AND statements. You could do this programmatically building up your query string (being careful to fully escape and user-provided data for security reasons). So, the resulting query would look like:

SELECT * 
FROM c 
WHERE 
    ARRAY_CONTAINS(c.Tags, "Tag1") AND 
    ARRAY_CONTAINS(c.Tags, "Tag3")

DocumentDB filter an array by an array

1 Answers