Group by date in mongoDB while counting other fields

Question

I've been using MongoDB for just a week and I have problems achieving this result: I want to group my documents by date while also keeping track of the number of entries that have a certain field set to a certain value.

So, my documents look like this:

    {
    "_id" : ObjectId("5f3f79fc266a891167ca8f65"),
    "recipe" : "A",
    "timestamp" : ISODate("2020-08-22T09:38:36.306Z")
    }

where recipe is either "A", "B" or "C". Right now I'm grouping the documents by date using this pymongo query:

mongo.db.aggregate(
    # Pipeline
    [
         # Stage 1
        {
            "$project": {
                "createdAt": {
                    "$dateToString": {
                        "format": "%Y-%m-%d",
                        "date": "$timestamp"
                    }
                },
                "progressivo": 1,
                "temperatura_fusione": 1
            }
        },
        # Stage 2
        {
            "$group": {
                "_id": {
                    "createdAt": "$createdAt"
                },
                "products": {
                    "$sum": 1
                }
            }
        },
        # Stage 3
        {
            "$project": {
                "label": "$_id.createdAt",
                "value": "$products",
                "_id": 0
            }
        }])

Which gives me results like this:

[{"label": "2020-08-22", "value": 1}, {"label": "2020-08-15", "value": 2}, {"label": "2020-08-11", "value": 1}, {"label": "2020-08-21", "value": 5}]

What I'd like to have is also the counting of how many times each recipe appears on every date. So, if for example on August 21 I have 2 entries with the "A" recipe, 3 with the "B" recipe and 0 with the "C" recipe, the desired output would be

{"label": "2020-08-21", "value": 5, "A": 2, "B":3, "C":0}

Do you have any tips?

Thank you!

Answer 1

You can do like following, what have you done is excellent. After that,

1. In second grouping, We just get total value and value of each recipe.
2. $map is used to go through/modify each objects
3. $arrayToObject is used to covert the array what we have done via map (key : value pair) to object
4. $ifNull is used for, sometimes your data might not have "A" or "B" or "C". But you need the value should be 0 if there is no name as expected output.

Here is the code

[
  {
    "$project": {
      "createdAt": {
        "$dateToString": {
          "format": "%Y-%m-%d",
          "date": "$timestamp"
        }
      },
      recipe: 1,
      "progressivo": 1,
      "temperatura_fusione": 1
    }
  },
  {
    "$group": {
      "_id": {
        "createdAt": "$createdAt",
        "recipeName": "$recipe",
        
      },
      "products": {
        $sum: 1
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.createdAt",
      value: {
        $sum: "$products"
      },
      recipes: {
        $push: {
          name: "$_id.recipeName",
          val: "$products"
        }
      }
    }
  },
  {
    $project: {
      "content": {
        "$arrayToObject": {
          "$map": {
            "input": "$recipes",
            "as": "el",
            "in": {
              "k": "$$el.name",
              "v": "$$el.val"
            }
          }
        }
      },
      value: 1
    }
  },
  {
    $project: {
      _id: 1,
      value: 1,
      A: {
        $ifNull: [
          "$content.A",
          0
        ]
      },
      B: {
        $ifNull: [
          "$content.B",
          0
        ]
      },
      C: {
        $ifNull: [
          "$content.C",
          0
        ]
      }
    }
  }
]

Working Mongo playground