MongoDB filter $lookup documents with date range

Question

I have a query that outputs like follow.

return ChallengeModel.aggregate([
    { $match: { is_processed: false,} },
    { $lookup: {
        from: 'scoreboards',
        let: { 'challengeId': '$_id' },
        pipeline: [{ $match: { $expr: { $eq: [ '$challenge_id', '$$challengeId' ]}} }],
        as: 'scoreboards'
    } }
]);

Output:

{
  "_id": "5b9679db0a3d61258aa3ffc3",
  "voucher_paid_by": "issuer",
  "start_date": "2018-09-03T00:00:00.000Z",
  "end_date": "2018-09-09T23:59:00.000Z",
  "accept_by_date": "2018-09-03T12:00:00.000Z",
  "scoreboards": [
    {
      "_id": "5b9757c68a6f1615d8e704d3",
      "user_id": "5b9667200a3d61258aa3ff9f",
      "date": "2018-09-01T00:00:00.000Z",
      "value": 2
    },
    {
      "_id": "5b9757c68a6f1615d8e704d3",
      "user_id": "5b9667200a3d61258aa3ff9f",
      "date": "2018-09-02T00:00:00.000Z",
      "value": 6
    },
    {
      "_id": "5b9757c68a6f1615d8e704d3",
      "user_id": "5b9667200a3d61258aa3ff9f",
      "date": "2018-09-03T00:00:00.000Z",
      "value": 5
    },
    {
      "_id": "5b9757c68a6f1615d8e704d3",
      "user_id": "5b9667200a3d61258aa3ff9f",
      "date": "2018-09-04T00:00:00.000Z",
      "value": 9
    },
    {
      "_id": "5b9757c68a6f1615d8e704d3",
      "user_id": "5b9667200a3d61258aa3ff9f",
      "date": "2018-09-05T00:00:00.000Z",
      "value": 7
    },

    {
      "_id": "5b9757c68a6f1615d8e704d3",
      "user_id": "5b9667200a3d61258aa3ff9f",
      "date": "2018-09-06T00:00:00.000Z",
      "value": 1
    }
  ]
}

I stripped away challenge_id from output so the output is just an example.

Now i want to perform same query where i only want scoreboard data which is in range of start_date and end_date.

So i did something like this.

return ChallengeModel.aggregate([
    { $match: { is_processed: false,} },
    { $lookup: {
        from: 'scoreboards',
        let: { 'challengeId': '$_id', 'startDate': '$start_date', 'endDate': '$end_date' },
        pipeline: [{ $match: { 
            date: { $gte: '$$startDate', $lt: '$$endDate' },
            $expr: { $eq: [ '$challenge_id', '$$challengeId' ]}} 
        }],
        as: 'scoreboards'
    } }
]);

But it returns empty array of scoreboard. Am i doing it right?

Answer 1

You need to use $expr with the other fields as well to $match the same fields of the documents.

ChallengeModel.aggregate([
  { "$match": { "is_processed": false,} },
  { "$lookup": {
    "from": "scoreboards",
    "let": { "challengeId": "$_id", "startDate": "$start_date", "endDate": "$end_date" },
    "pipeline": [
      { "$match": {
        "$expr": {
          "$and": [
            { "$eq": [ "$challenge_id", "$$challengeId" ] },
            { "$gte": [ "$date", "$$startDate" ] },
            { "$lt": [ "$date", "$$endDate" ] }
          ]
        }
      }}
    ],
    "as": "scoreboards"
  }}
])