Luke McGregor Luke McGregor - 1 month ago 20
C# Question

Mongodb Query for elements in a sub-document which match a condition

I have a document that looks something like this:

{
"spans" : [
{
"from" : ISODate("2016-01-01T00:00:00.000+0000"),
"to" : ISODate("2016-01-02T00:00:00.000+0000")
},
{
"from" : ISODate("2016-01-03T00:00:00.000+0000"),
"to" : ISODate("2016-01-04T00:00:00.000+0000")
},
{
"from" : ISODate("2016-02-01T00:00:00.000+0000"),
"to" : ISODate("2016-02-04T00:00:00.000+0000")
}
]
}


I want to query for a document but only return spans with a from greater than 2016-1-2

I first tried $elemMatch but it only returns the first matching span.

It seems like the $filter operator is the right way to do this but I cant seem to find any methods on the ProjectionBuilder to do a filter.

I think I want to do a query like this:

.aggregate([
{
$match:{/*Some other filter*/}
},
{
$project: {
Spans: {
$filter: {
input: "$spans",
as: "span",
cond: { $gt: [ "$$span.from", ISODate("2016-01-02T00:00:00Z") ] }
}
}
}
}
])


I think it should look like this but there is no filter method

var pb = new ProjectionDefinitionBuilder<MyObj>();

var projection = pb.Combine(new List<ProjectionDefinition<MyObj>> {
pb.Filter(x => x.Spans, s => s.From > from))//no such thing...
});


var result= _collection.
.Match(x=>/*my match*/)
.Project<MyObj>(projection)
.ToList();


What am I missing? Is it something other than the ProjectionBuilder I should be using?

Answer

There are two, may be more ways to solve this problem. You can use $project with $filter or plain old and trusty $unwind.

I'm coding an example using shell query and quite sure you can easily translate into C#.

Solution #1

db.collection.aggregate([
  {$unwind:"$spans"},
  {$match: {"spans.from": {$gt: ISODate("2016-01-02T00:00:00.000+0000")}}},
  {$group:{_id:"$_id", spans:{$push:"$spans"}}}
])

Solution #2 Supported on MongoDB version 3.2+ only

db.collection.aggregate([
  {$project: 
    {spans:
      {$filter:
        {input:"$spans", as: "spans", cond:
          {$gt: ["$$spans.from", ISODate("2016-01-02T00:00:00.000+0000")]}
        }
      }
    }
  }
])

Examples above will filter out all array elements less than or equals to ISODate("2016-01-02T00:00:00.000+0000") in from leaving end result as:

{ 
    "_id" : ObjectId("5804123984d3eb497d538a6e"), 
    "spans" : [
        {
            "from" : ISODate("2016-01-03T00:00:00.000+0000"), 
            "to" : ISODate("2016-01-04T00:00:00.000+0000")
        }, 
        {
            "from" : ISODate("2016-02-01T00:00:00.000+0000"), 
            "to" : ISODate("2016-02-04T00:00:00.000+0000")
        }
    ]
}
Comments