1

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.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Ofir Z
  • 95
  • 1
  • 11

1 Answers1

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();
shA.t
  • 16,580
  • 5
  • 54
  • 111
  • Hey, first of all thanks a lot. This seems to work as intended and looks great. Is it possible get results back as a "MyItem" class instead of BsonDocument? Or do I have to deserialize the BsonDocument after performing the query? – Ofir Z Dec 19 '16 at 08:02
  • Results are in a new type, so you can't get them as `MyItem`, But you can define a new class as same as results properties ;). – shA.t Dec 19 '16 at 08:56