Get all items where subDocument.value in listOfStrings

Question

I am using the MongoDB.Driver nuget package in dotnetcore 2.1. I am trying to return a list of documents in a collection where a subdocument field is equal to any items contained within a list that I have. Ideally I need this in C# syntax for the MongoDB.Driver nuget package for dotnetcore 2.1.

Document { "_id" : "e29628a65e914c1e91b3fd9cbf6f2353", "Enabled" : true, "Name" : "Document123", "DisplayName" : "Some Document", "Description" : "Some Description", "Data" : [ "lastname", "email", "firstname", "zipcode" ], "Items" : [ { "_id" : "1", "Name" : "bob" }, { "_id" : "2", "Name" : "smith" } ] }

If this were SQL, here is what I am trying to do:

SELECT * FROM Document a, Item b WHERE a.Id = b.DocumentId AND b.Name IN ('bob', 'smith', 'alex')

Here is what we have that is not working with the MongoDB driver:

string[] names = new [] { "bob", "smith", "alex" };
var document = new BsonDocument()
{
                new BsonElement("Items.Name", new BsonDocument()
                {
                    new BsonElement("$in", new BsonArray(names))
                })
            };
var itemsQuery = collection
                .Aggregate()
                .Match(document)
                ;

            var items = itemsQuery.ToList();

Thanks in advance.

Answer 1

It turns out we had to "unwind" and run a match query after this. Here is the code that worked for us.

        var collection = GetCollection<Document>();

        var document = new BsonDocument()
        {
            new BsonElement("Name", "Document123"),
            new BsonElement("Items.Name", new BsonDocument()
            {
                new BsonElement("$in", new BsonArray(new [] { "bob", "smith", "alex"}))
            })
        };

        var itemsQuery = collection
            .Aggregate()
            .Unwind(d => d.Items)
            .Match(document)
            ;

        var items = itemsQuery.ToList();

Answer 2

Two initial remarks:

a) Your query should include a $match stage at the start to speed up things. It might feel odd to kind of $match twice but with a large number of documents you will see a major difference because you end up with a lot less documents in the $unwind stage.

b) You can write a string based query with way less code like this:

var itemsQuery = collection
    .Aggregate()
    .Unwind(document => document.Items)
    .Match("{ 'Name' : 'Document123', 'Items.Name' : { $in: [ '" + string.Join("', '", names) + "' ] } }");

But you can write all of this in a kind of type-safe version (as in "not using strings or BsonDocument") like this:

/// <summary>
/// This would be just to avoid having to create two almost identical types which, however, could be done as well if you don't like abstract base types
/// </summary>
public abstract class DocumentBase
{
    public string Id { get; set; }
    public string Name { get; set; }
    // this is where all other fields would go like "Enabled", "DisplayName", etc...
}

/// <summary>
/// Represents the <see cref="DocumentBase"/> type but with an unwound <see cref="Items"/> property which is actually a single <see cref="Item"/>,
/// </summary>
public class UnwoundDocument : DocumentBase
{
    public Item Items { get; set; }
}

/// <summary>
/// This is the real "Document" type with a <see cref="List{Item}"/> property called <see cref="Items"/>.
/// </summary>
public class Document : DocumentBase
{
    public List<Item> Items { get; set; }
}

/// <summary>
/// This would hold all properties of an Item - I've dropped the "Id" property since it's not needed for this sample
/// </summary>
public class Item
{
    public string Name { get; set; }
}

With those types in place you can run the below code:

public class Program
{
    static void Main(string[] args)
    {
        var collection = new MongoClient().GetDatabase("just_a_test").GetCollection<Document>("Document");
        // clear out all existing documents to allow for multiple runs of this sample app
        collection.DeleteMany(FilterDefinition<Document>.Empty);

        // insert our test document
        collection.InsertOne(new Document { Id = "some id", Name = "Document123", Items = new List<Item> { new Item { Name = "bob" }, new Item { Name = "smith" } } });

        // create a bunch of filters
        var namesToSearchFor = new List<string> { "bob", "smith", "alex" };
        Expression<Func<Item, bool>> itemFilter = item => namesToSearchFor.Contains(item.Name);
        FilterDefinitionBuilder<Document> fdb = Builders<Document>.Filter;
        FilterDefinition<Document> documentFilter = fdb.Eq(f => f.Name, "Document123") & fdb.ElemMatch(f => f.Items, itemFilter);

        // construct the aggregation pipeline
        IAggregateFluent<UnwoundDocument> pipeline = collection
            .Aggregate()
            .Match(documentFilter) // filter first for performance reasons (technically not needed)
            .Unwind(document => document.Items) // flatten the "Items" array
            .As(BsonSerializer.SerializerRegistry.GetSerializer<UnwoundDocument>()) // this is to tell the driver that the resulting document will look like our "UnwoundDocument" type
            .Match(d => namesToSearchFor.Contains(d.Items.Name)); // just so we can filter again and use the result in a nicely type-safe manner

        // just print out all results for demo purposes
        foreach (var result in pipeline.ToList())
        {
            Console.WriteLine(result.ToJson());
        }
    }
}