Im new to mongoDB, so having some difficulties filtering my collections as I need.

I have this collection

[
  {
    "id": "sdfsdfsdf",
    "key": "tryrtyrty",
    "createdAt": "2017-01-28T01:22:14.398Z",
    "counts": [
      170
    ],
    "value": "Something"
  },
  {
    "id": "hjmhjhjm",
    "key": "yuiyuiyui",
    "createdAt": "2017-01-28T01:22:14.398Z",
    "counts": [
      150,
      160
    ],
    "value": "Something"
  }
]

I want to filter by range of dates (min-max date) and range of counts, meaning I want to give a min and max value for the totalCount of the sum in the field. Example, I would like to filter results whose min counts sum is 200 and max 400. This would only return the second result (the sum is 310, while the first result the sum is 170).

Right now I have this:

db.collection.aggregate([
  {
    $project: {
      totalCount: {
        $sum: {
          "$filter": {
            "input": "$counts",
            "as": "bla",
            "cond": {
              "$gte": [
                "$sum", // I think the error is here, I dont know how to reference the sum of the list
                300 //I want records whose count sum is more than this value
              ]
            }
          }
        }
      }
    }
  }
])

This returns all the records with TotalCount on 0, which is not want I want, I would like the records matching the count condition with the correct TotalCount (and eventually matching the dates as well)

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "totalCount": 0
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "totalCount": 0
  }

Desired output

[
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "totalCount": 310,
    "key": "yuiyuiyui",
    "createdAt": "2017-01-28T01:22:14.398Z"
  }
]

Any help would be greatly appreciated. Even more if it comes with both dates and count filter.

CodePudding user response：

You should not use $filter as it doesn't suitable for this scenario.

Stages:

1. set - Create totalCounts with $sum all elements in counts array.
2. $match - Fiter the documents which has totalCounts within the range.
3. $unset - Remove fields for decorating output document.

db.collection.aggregate([
  {
    $set: {
      "totalCounts": {
        $sum: "$counts"
      }
    }
  },
  {
    $match: {
      totalCounts: {
        $gte: 200,
        $lte: 400
      }
    }
  },
  {
    $unset: [
      "counts",
      "id"
    ]
  }
])

Sample Mongo Playground

For date range filter, you need $expr and $and operator as below

{
  $match: {
    totalCounts: {
      $gte: 200,
      $lte: 400
    },
    $expr: {
      $and: [
        {
          $gte: [
            {
              "$toDate": "$createdAt"
            },
            /* Date from */
          ]
        },
        {
          $lte: [
            {
              "$toDate": "$createdAt"
            },
            /* Date to */
          ]
        }
      ]
    }
  }
}

Sample Mongo Playground (with date range filter)