Nested grouping in Mongodb-CodePudding

I am working on grouping the MongoDB documents and updating the sample in the below link

https://mongoplayground.net/p/c2s8KmavPBp

I am expecting the output to look like

[
  {
    "group" : "one",
      "details":[{
        "avgtime" : 9.359833333333333,
        "title": "Scence 1"
    },{
        "avgtime" : 9.359833333333333,
        "title": "Scence 2"
    },{
        "avgtime" : 9.359833333333333,
        "title": "Scence 3"
    }]
  },
  {
    "group" : "two",
      "details":[{
        "avgtime" : 9.359833333333333,
        "title": "Scence 1"
    }]
  }
  ]

How to rename the field _id to the group and merge the two elements containing title: scence 1 and display their average time

Note: Question and sample link updated

CodePudding user response：

Query

group by both field, to do the calculation
group by the one field and use the calculated avgtime
group the specific(here 2 fields) first and then the more general, using the values from the specific

Test code here

aggregate(
[{"$group": 
   {"_id": {"group": "$group", "title": "$details.title"},
    "avgtime": 
      {"$avg": 
        {"$divide": [{"$subtract": ["$endTime", "$startTime"]}, 60000]}}}},
 {"$group": 
   {"_id": "$_id.group",
    "group": {"$first": "$_id.group"},
    "details": {"$push": {"title": "$_id.title", "avgtime": "$avgtime"}}}},
 {"$unset": ["_id"]}])