Consider the following collection of people using their phone:

{
     "_id": "1",
     "name": "John",
     "calls": [
        0: 2022-07-01T01:05:00.000 00:00,
        1: 2022-07-08T02:14:00.000 00:00,
        2: 2022-07-08T05:27:00.000 00:00,
        2: 2022-07-09T03:46:00.000 00:00,
     ]
},
{
     "_id": "1",
     "name": "George",
     "calls": [
        0: 2022-06-28T01:05:00.000 00:00,
        1: 2022-06-29T02:14:00.000 00:00,
        2: 2022-06-29T05:27:00.000 00:00,
        2: 2022-07-02T03:46:00.000 00:00,
     ]
}

I want to get all the people who have had less than 2 calls in the last week. How could I do that?

As I understand it, the filter would have to:

1. Take all the calls that have been made in the last two calls.
2. Aggregate (count) them.
3. If the count is less than 2, return the person.

I've done the filtering aggregation in the projection:

projection: {
    callsNumber: {
        $size: { 
            $filter: { 
                input: "$calls", 
                cond: { 
                    $gte: ["$$this", ISODate("2022-07-03T00:00:00.000 00:00")]
                }
            }
        }
    }
}

But I've no idea how to use a projected field in the filtering, or how to translate that kind of projection into the filter.

CodePudding user response：

As pointed out in the comment, you can use your projected field, in the $match field just like any other field, like this:

db.collection.aggregate([
  {
    "$project": {
      callsNumber: {
        $size: {
          $filter: {
            input: "$calls",
            cond: {
              $gte: [
                "$$this",
                ISODate("2022-07-03T00:00:00.000 00:00")
              ]
            }
          }
        }
      },
      name: 1
    }
  },
  {
    "$match": {
      "callsNumber": {
        $lt: 2
      }
    }
  }
])

Here's the playground link.