CODEWITHSUNDEEP
c#mongodb
undefined
undefined

Reputation: 34248

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?

Upvotes: 0

Views: 868

Answers (1)

Saleem
Saleem

Reputation: 8978

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")
        }
    ]
}

Upvotes: 2

Related Questions