Filter multiple parameters with MongoDB provider

Question

I have the below document schema.

{
      "name":"Name",
      "region":"New Jersey",
      "address":"92 Something Rd",
      "city":"Jersey City",
      "state":"NJ",
      "zipCode":"07302",
      "country":"USA",
      amenities":[
         "Sauna",
         "Locker",
         "Shop"
      ],
      "services":[
         "Car Rental",
         "Transportation"
      ]
}

I want with one call to the server to get all the documents matching any of the filter arguments where map 1-1 meaning "state" = "NJ" OR "city" = "Jersey City" but also when any value of the list contained to any document array child, example [ "Sauna", "Locker" ] ANY IN "amenities". And it should be an OR concatenation of all the possible filters.

Using the C# MongoDB Driver I came up with the below methods so far in a MongoRepository class but doesn't return the desired results.

public async Task<IEnumerable<T>> DocumentsMatchEqFieldValueAsync<T>(string collectionName,
            IDictionary<string, string> fieldsValues = null,
            IDictionary<string, IEnumerable<string>> fieldsWithEnumerableValues = null,
            IEnumerable<ObjectId> ids = null)
{
    var cursor = await GetEqAsyncCursor<T>(collectionName, fieldsValues, fieldsWithEnumerableValues, ids).ConfigureAwait(false);
    return await cursor.ToListAsync().ConfigureAwait(false);
}

protected Task<IAsyncCursor<T>> GetEqAsyncCursor<T>(string collectionName, 
            IDictionary<string, string> fieldsValues = null,
            IDictionary<string, IEnumerable<string>> fieldsWithEnumerableValues = null,
            IEnumerable<ObjectId> ids = null)
{
    var collection = GetCollection<T>(collectionName);
    var builder = Builders<T>.Filter;

    // Not sure if this is the correct way to initialize it because it seems adding an empty filter condition returning ALL document;
    FilterDefinition<T> filter = new BsonDocument(); 

    if (fieldsValues != null &&
        fieldsValues.Any())
    {
        filter = filter | fieldsValues
                    .Select(p => builder.Eq(p.Key, p.Value))
                    .Aggregate((p1, p2) => p1 | p2);
    }

    if (fieldsWithEnumerableValues != null &&
        fieldsWithEnumerableValues.Any())
    {
        filter = filter | fieldsWithEnumerableValues
                    .Select(p => builder.AnyEq(p.Key, p.Value))
                    .Aggregate((p1, p2) => p1 | p2);
    }

    if (ids != null &&
        ids.Any())
    {
        filter = filter | ids
                .Select(p => builder.Eq("_id", p))
                .Aggregate((p1, p2) => p1 | p2);
    }
    return collection.FindAsync(filter);
}

I want it to be generic so the client can call the method like this.

public async Task should_return_any_records_matching_all_possible_criteria()
{
    // Arrange
    IDocumentRepository documentRepository = new MongoRepository(_mongoConnectionString, _mongoDatabase);

    // Act
    var documents = await documentRepository.DocumentsMatchEqFieldValueAsync<BsonDocument>(Courses,
                fieldsValues: new Dictionary<string, string>
                {
                    { "state", "NJ" },
                    { "city", "Jersey City" }
                },
                fieldsWithEnumerableValues: new Dictionary<string, IEnumerable<string>>
                {
                    { "services", new List<string> { "Car Rental", "Locker" } },
                    { "amenities", new List<string> { "Sauna", "Shop" } }
                });

    // Assert
    documents.ShouldNotBeEmpty();
}

I would expect documents that have "state" = "NJ" OR "city" = "Jersey City" OR "services" CONTAINS ANY OF "Car Rental", "Locker" OR "amenities" CONTAINS ANY OF "Sauna", "Shop".

Answer 1

I am posting below the method I ended up using after some research for the future help of anyone looking to do the same. I found how to query using regex here, write plain MongoDB queries and add them to the filter collection here, and how to debug the generated query here.

After having all this information and a little bit of experimentation using the Studio 3T client find below the method.

protected Task<IAsyncCursor<T>> GetEqAsyncCursor<T>(string collectionName,
            IDictionary<string, string> fieldEqValue = null,
            IDictionary<string, string> fieldContainsValue = null,
            IDictionary<string, IEnumerable<string>> fieldEqValues = null,
            IDictionary<string, IEnumerable<string>> fieldElemMatchInValues = null,
            IEnumerable<ObjectId> ids = null)
{
    var collection = GetCollection<T>(collectionName);
    var builder = Builders<T>.Filter;

    IList<FilterDefinition<T>> filters = new List<FilterDefinition<T>>();

    if (fieldEqValue != null &&
        fieldEqValue.Any())
    {
        filters.Add(fieldEqValue
                    .Select(p => builder.Eq(p.Key, p.Value))
                    .Aggregate((p1, p2) => p1 | p2));
    }

    if (fieldContainsValue != null &&
        fieldContainsValue.Any())
    {
        filters.Add(fieldContainsValue
                    .Select(p => builder.Regex(p.Key, new BsonRegularExpression($".*{p.Value}.*", "i")))
                    .Aggregate((p1, p2) => p1 | p2));
    }

    if (fieldEqValues != null &&
        fieldEqValues.Any())
    {
        foreach (var pair in fieldEqValues)
        {
            foreach (var value in pair.Value)
            {
                filters.Add(builder.Eq(pair.Key, value));
            }
        }
    }

    if (fieldElemMatchInValues != null &&
        fieldElemMatchInValues.Any())
    {
        var baseQuery = "{ \"%key%\": { $elemMatch: { $in: [%values%] } } }";
        foreach (var item in fieldElemMatchInValues)
        {
            var replaceKeyQuery = baseQuery.Replace("%key%", item.Key);
            var bsonQuery = replaceKeyQuery.Replace("%values%", 
                        item.Value
                            .Select(p => $"\"{p}\"")
                            .Aggregate((value1, value2) => $"{value1},
 {value2}"));
            var filter = BsonSerializer.Deserialize<BsonDocument>(bsonQuery);
            filters.Add(filter);
        }
    }

    if (ids != null &&
        ids.Any())
    {
        filters.Add(ids
                .Select(p => builder.Eq("_id", p))
                .Aggregate((p1, p2) => p1 | p2));
    }

    var filterConcat = builder.Or(filters);

    // Here's how you can debug the generated query
    //var documentSerializer = BsonSerializer.SerializerRegistry.GetSerializer<T>();
    //var renderedFilter = filterConcat.Render(documentSerializer, BsonSerializer.SerializerRegistry).ToString();

    return collection.FindAsync(filterConcat);
}