I'm trying to implement a nested group query in mongodb and I'm getting stuck. How to group data by date and campaign_id here ? Each campaign_id should contain a nested array of creatives with data (views and clicks) for each creative.

The data looks like this: Data:

[
  {
    "_id": ObjectId("61e3b88d3f14fb87161651c7"),
    "campaign_id": 41,
    "creative_id": 143,
    "date": ISODate("2022-01-16T00:00:00Z"),
    "views": 1
  },
  {
    "_id": ObjectId("61e3b8cd3f14fb87161651ef"),
    "campaign_id": 41,
    "creative_id": 143,
    "date": ISODate("2022-01-16T00:00:00Z"),
    "views": 7070,
    "clicks": 241
  },
  {
    "_id": ObjectId("61e3b8cd3f14fb87161651f2"),
    "campaign_id": 41,
    "creative_id": 144,
    "date": ISODate("2022-01-16T00:00:00Z"),
    "views": 3474,
    "clicks": 52
  },
  {
    "_id": ObjectId("61e3b8cd3f14fb87161651f4"),
    "campaign_id": 41,
    "creative_id": 146,
    "date": ISODate("2022-01-16T00:00:00Z"),
    "views": 3595,
    "clicks": 52
  },
  {
    "_id": ObjectId("61e3b8cd3f14fb87161651f6"),
    "campaign_id": 41,
    "creative_id": 145,
    "date": ISODate("2022-01-16T00:00:00Z"),
    "views": 3561,
    "clicks": 227
  },
  {
    "_id": ObjectId("61e4b18a3f14fb871616d8b0"),
    "campaign_id": 41,
    "creative_id": 143,
    "date": ISODate("2022-01-17T00:00:00Z"),
    "views": 9528,
    "clicks": 104
  },
  {
    "_id": ObjectId("61e4b18a3f14fb871616d8b2"),
    "campaign_id": 41,
    "creative_id": 145,
    "date": ISODate("2022-01-17T00:00:00Z"),
    "views": 4699,
    "clicks": 100
  },
  {
    "_id": ObjectId("61e4b18a3f14fb871616d8b5"),
    "campaign_id": 41,
    "creative_id": 146,
    "date": ISODate("2022-01-17T00:00:00Z"),
    "views": 4759,
    "clicks": 36
  },
  {
    "_id": ObjectId("61e4b18e3f14fb871616d8ca"),
    "campaign_id": 41,
    "creative_id": 144,
    "date": ISODate("2022-01-17T00:00:00Z"),
    "views": 4822,
    "clicks": 27
  }
]

This is the expected result after grouping: Expected result:

{
    "_id": {
      "campaign": 955,
      "date": ISODate("2022-01-16T00:00:00Z")
    },
    "creative": [
      143: {clicks: XXXX, views:XXXX}
      146: {clicks: XXXX, views:XXXX},
      142: {clicks: XXXX, views:XXXX},
      ...

    ]
  },

https://mongoplayground.net/p/oZNxQNb250R

CodePudding user response：

You can do something like this:

db.collection.aggregate([
  {
    "$sort": {
      "date": 1
    }
  },
  {
    "$group": {
      "_id": {
        date: "$date",
        campaign: "$campaign_id",
        creative_id: "$creative_id"
      },
      views: {
        $sum: "$views"
      },
      clicks: {
        $sum: "$clicks"
      }
    }
  },
  {
    "$group": {
      "_id": {
        date: "$_id.date",
        campaign: "$_id.campaign"
      },
      data: {
        $push: {
          k: {
            "$toString": "$_id.creative_id"
          },
          v: {
            views: "$views",
            clicks: "$clicks"
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 1,
      creative: {
        "$arrayToObject": "$data"
      }
    }
  }
])

Mongo Playground

Notice I added an additional $group stage, this is done to group by creative_id and sum all it's views and clicks. if this stage is redundant you can just eliminate this stage however it works either way.