MongoDB C# Query: Filter + Aggregation + Checking if value is true/false

Question

I need to perform a pretty complex MongoDB query and I'm having a really hard time being able to narrow the entire thing down to one query, although I do think it's doable I don't really have enough experience with MongoDB to get it quite right and I'd really appreciate some help.

My class looks something like this:

class MyItem
{
    public int ID { get; set; }

    public int Value { get; set; }

    public bool IsDropped { get; set; }
}

I need to be able to select the min value for each ID that isn't dropped. For example:

items.Add(new MyItem() { ID = 1, Value = 100, IsDropped = true });
items.Add(new MyItem() { ID = 1, Value = 150, IsDropped = false });
items.Add(new MyItem() { ID = 1, Value = 200, IsDropped = true });
items.Add(new MyItem() { ID = 2, Value = 100, IsDropped = false });
items.Add(new MyItem() { ID = 2, Value = 250, IsDropped = false });

For these items, the values I want returned are:

ID: 1, Value: 150, IsDropped: false
ID: 2, Value: 100, IsDropped: false

However, if all values for a certain ID are dropped, I want to be able to know that as well so for example for these values:

items.Add(new MyItem() { ID = 2, Value = 100, IsDropped = true });
items.Add(new MyItem() { ID = 2, Value = 150, IsDropped = true });

I want to get:

ID: 2, Value: (doesn't really matter), IsDropped: true

Also on top of that, I need to be able to perform simple filter queries for example "only return items where ID == 1 || ID == 2"

Can this be done in a single query? I'm able to aggregate the class based on minimum value but adding the IsDropped parameter into the mix is making it really hard for me to write a single query that can perform all of this.

Thanks in advance for the help.

Answer 1

I think this can help you:

var groupBy = new BsonDocument
{
    {"_id", "$ID"},
    {
        "min", new BsonDocument("$min", new BsonDocument
        {
            {"IsDropped", "$IsDropped"},   //This line will do the trick ;)
            {"Value", "$Value"}
        })
    }
};
var results = collection.Aggregate().Group(groupBy).ToList();

And to add a filter over grouping results use this:

// `where ID == 1 || ID == 2` is as same as `where ID IN (1,2)`
var having = Builders<BsonDocument>.Filter.In("_id", new[] { 1, 2 }); 

// Now put having after groupBy
var results = collection.Aggregate().Group(groupBy).Match(having).ToList();