I want to print grouped by date and by "productId" within the date. In this example, the output should be as follow:

[
  {
    "_id": "2018-03-04",
    "product1": 2,
    "product2": 2
  }
]

Data: https://mongoplayground.net/p/gzvm11EIPn2

How to make it?

CodePudding user response：

When you use the $group stage in aggregation you learn to group by one field as such: { $group: { "_id": "$field1"...

When you want to group by two or more fields "_id" needs to be a subdocument and you pass the fields as key value pairs inside the subdocument as such:

db.mycollection.aggregate([
    {
        $group:
        {
            "_id": { "product1": "$product1", "product2": "$product2", ... }
        }
    }
])

... etc.

CodePudding user response：

1. $group - Group by createdAt (date string) and productId and perform count via $sum.

2. $group - Group by createdAtand push data from (1) to products array field.

3. $replaceRoot - Replace input document with new document.

   3.1. $arrayToObject - Convert the object from products array field to key value pair with productId (key) and count (value).

   3.2. $mergeObjects - Create object with _id and merge the object from (3.2) into 1 object.

db.collection.aggregate([
  {
    $group: {
      _id: {
        createdAt: {
          $dateToString: {
            format: "%Y-%m-%d",
            date: "$createdAt"
          }
        },
        productId: "$productId"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.createdAt",
      products: {
        $push: {
          productId: "$_id.productId",
          count: "$count"
        }
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          {
            _id: "$_id"
          },
          {
            $arrayToObject: {
              $map: {
                input: "$products",
                in: {
                  k: {
                    $toString: "$$this.productId"
                  },
                  v: "$$this.count"
                }
              }
            }
          }
        ]
      }
    }
  }
])

Sample Mongo Playground

Output

[
  {
    "5e345223b3aa703b8a9a4f34": 2,
    "5e345223b3aa703b8a9a4f35": 2,
    "_id": "2018-03-04"
  }
]