I want to group by date, but how can I do it in mongodb?-CodePudding

The data type is as follows.

_id: ObjectId
details: array
     0: Object
        salesManagerId: ObjectId
createdAt: Date

The data type is as follows.

What I want is,

Date: Group by date. Sorts in descending order through the results grouped by year, month, and day. As a sorted result, it should be sorted in the same order as 20211020, 20211019 .
Sales: It should be output how many _ids are included by date grouped by date. I think it's equivalent to SELECT COUNT(*) in mysql.
Own Sales Volume: Among the grouped counts, the number including details should be output. However, if specific obejctId is included in details.salesManagerId, it should be excluded.

I expect below table.

Date       Sales    Own Sales Volume
20211020   30       11
20211019   15        2
20211018   23        3
.
.
.

In particular, I am having a hard time grouping by date.

CodePudding user response：

$group

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        year: {
          $year: "$createdAt"
        },
        month: {
          $month: "$createdAt"
        },
        day: {
          $dayOfMonth: "$createdAt"
        }
      },
      "Sales": {
        "$sum": 1
      },
      "Own Sales Volume": {
        "$sum": {
          $cond: [
            {
              $and: [
                {
                  $isArray: "$details"
                },
                {
                  $gt: [
                    {
                      "$size": "$details"
                    },
                    0
                  ]
                }
              ]
            },
            1,
            0
          ]
        }
      }
    }
  }
])

mongoplayground