How to group data by time period?

Question

I have data on invoices and I want to group them by time period wall. for example 0-10 and 11-20:

I need to solve them with $aggregate in mongoDB.

{
    '_id': '1',
    'value': 10,
    'due_date': '20221001'
},
{
    '_id': '2',
    'value': 10,
    'due_date': '20221012'
},
{
    '_id': '2',
    'value': 10,
    'due_date': '20221030'
},

I need to group by period 0-10 days, 11-20 days and sum the values: For the example above the result would be:

[{
    "_id": '0-10 days',
    "total": 10,
},
{
    "_id": '11-20 days',
    "total": 10,
},
{
    "_id": '>20 days',
    "total": 10,
}]

I try with:

['$facet' => [
  ['due_date_one' => [
      ['$match' => [
         'due_date' => [
           '$gt'    => new UTCDateTime((new Carbon())-> subDays(100) -> getTimestamp()),
            '$lte'  => date('Y-m-d', strtotime('now'))
         ]
         ]
     ]],
]]

Answer 1

You could use $bucket or add a few $addFields and $group stages:

Example mongo playground - https://mongoplayground.net/p/B0zl_GGH4sG

Example Documents:

[
  {
    "_id": "1a",
    "value": 10,
    "due_date": "20221001"
  },
  {
    "_id": "1b",
    "value": 5,
    "due_date": "20221102"
  },
  {
    "_id": "1c",
    "value": 7,
    "due_date": "20221102"
  },
  {
    "_id": "2a",
    "value": 10,
    "due_date": "20221012"
  },
  {
    "_id": "2b",
    "value": 7,
    "due_date": "20221113"
  },
  {
    "_id": "2c",
    "value": 8,
    "due_date": "20221113"
  },
  {
    "_id": "3a",
    "value": 10,
    "due_date": "20221030"
  },
  {
    "_id": "3b",
    "value": 9,
    "due_date": "20221131"
  },
  {
    "_id": "3c",
    "value": 11,
    "due_date": "20221131"
  }
]

Aggregation query:

db.collection.aggregate([
  {
    $addFields: {
      day: {
        $toInt: { $substr: [ "$due_date", 6, 2 ] }
      }
    }
  },
  {
    $addFields: {
      bucketDate: {
        $switch: {
          branches: [
            { case: { $gt: [ "$day", 20 ] }, then: ">20 days" },
            { case: { $gt: [ "$day", 10 ] }, then: "11-20 days" }
          ],
          "default": "0-10 days"
        }
      }
    }
  },
  {
    $addFields: {
      bucketDateWithMonth: {
        $concat: [
          { $substr: [ "$due_date", 0, 6 ] },
          " ",
          "$bucketDate"
        ]
      }
    }
  },
  {
    $group: {
      //_id: "$bucketDate", //No grouped month
      _id: "$bucketDateWithMonth", //With grouped month
      count: { $sum: 1 },
      value: { $sum: "$value" }
    }
  }
])

Output: (grouped month)

[
  {
    "_id": "202210 0-10 days",
    "count": 1,
    "value": 10
  },
  {
    "_id": "202211 0-10 days",
    "count": 2,
    "value": 12
  },
  {
    "_id": "202210 11-20 days",
    "count": 1,
    "value": 10
  },
  {
    "_id": "202211 11-20 days",
    "count": 2,
    "value": 15
  },
  {
    "_id": "202210 \u003e20 days",
    "count": 1,
    "value": 10
  },
  {
    "_id": "202211 \u003e20 days",
    "count": 2,
    "value": 20
  }
]

Output: (No grouped month)

[
  {
    "_id": "\u003e20 days",
    "count": 3,
    "value": 30
  },
  {
    "_id": "0-10 days",
    "count": 3,
    "value": 22
  },
  {
    "_id": "11-20 days",
    "count": 3,
    "value": 25
  }
]