CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Canolyb1
Canolyb1

Reputation: 724

Why is $match returning item but filter is filtering it

I have a collection named 'agendas' each agenda has a nested tasks collection winch is referenced object id.

I'm doing a lookup on the tasks collection and then doing a match based on a date range to find all the agenda "items" with tasks between that range. I then do a filter to remove all the tasks. So basically the $match and $filter are the same.

I'm not sure why I'm returning a agenda item with completely filtered tasks[]. I thought any agenda items would be filtered by the $match first.

My Query:

db.agendas.aggregate([ 
{ '$lookup': { 
    from: 'tasks', 
    localField: 'tasks', 
    foreignField: '_id', 
    as: 'tasks' } }, 
{ '$match': { 
    '$and': [ 
    { 'tasks.status': 'Closed' }, 
    { 'tasks.date': { '$gte': new ISODate('2017-10-01T05:00:00.000Z') } }, 
    { 'tasks.date': { '$lte': new ISODate('2017-10-01T05:00:00.000Z') } } 
    ] } },
{ '$limit': 100 }, 
{ '$project': { 
    type: 1, description: 1,
    tasks: { 
        '$filter': { 
            input: '$tasks', 
            as: 'tasks', 
            cond: { '$and': [ 
                { '$eq': [ '$$tasks.status', 'Closed' ] }, 
                { '$gte': [ '$$tasks.date', new ISODate('2017-10-01T05:00:00.000Z') ] }, 
                { '$lte': [ '$$tasks.date', new ISODate('2017-10-01T05:00:00.000Z') ] } 
                ] } } } } }, 
{ '$group': { _id: '$type', agenda: { '$push': '$$ROOT' } } },
{ '$sort': { _id: 1 } } ], {}).pretty()

Results

{
        "_id" : "Maintenance",
        "agenda" : [
                {
                        "_id" : ObjectId("59d429ba15c67c147f8f3513"),
                        "description" : "Some new item 4",
                        "type" : "Maintenance",
                        "tasks" : [ ]
                }
        ]
}
{
        "_id" : "Monitoring",
        "agenda" : [
                {
                        "_id" : ObjectId("59d50d36e6de730a6d85019b"),
                        "description" : "Some new test agenda for Tomski",
                        "type" : "Monitoring",
                        "tasks" : [
                                {
                                        "_id" : ObjectId("59d5378808f51d0c6590c724"),
                                        "status" : "Closed",
                                        "text" : "Some task 2",
                                        "author" : {
                                                "id" : ObjectId("59c09f8a8b4ad70aa4cda487"),
                                                "name" : "Karunik De"
                                        },
                                        "date" : ISODate("2017-10-01T05:00:00Z"),
                                        "created" : ISODate("2017-10-04T19:33:28.560Z"),
                                        "__v" : 0
                                }
                        ]
                }
        ]
}

Upvotes: 0

Views: 25

Answers (1)

s7vr
s7vr

Reputation: 75934

You need to use $elemMatch when you have multiple criteria and you would like to apply all criteria on each task.

Something like 

{
  "$match": {
    "tasks": {
      "$elemMatch": {
        "status": "Closed",
        "date": {
          "$gte":  new ISODate('2017-10-01T05:00:00.000Z'),
          "$lte":  new ISODate('2017-10-01T05:00:00.000Z')
        }
      }
    }
  }
}

More here

Compare with below which looks for one task matching each criteria. So you can have single task matching all criterias or three different tasks matching each of criteria to return document.

{
  "$match": {
    "tasks.status": "Closed",
    "tasks.date": {
      "$gte": new ISODate('2017-10-01T05:00:00.000Z'),
      "$lte": new ISODate('2017-10-01T05:00:00.000Z')
    }
  }
}

More here

Upvotes: 1

Related Questions