Nested grouping in Mongodb

Question

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

Takis · Accepted Answer · 2022-04-09 16:31:27Z

1

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

PlayMongo

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"]}])

edited Apr 9, 2022 at 16:31

answered Oct 28, 2021 at 22:09

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user15111949 Over a year ago

It works !! thanks. Got $unset Unrecognized pipeline stage name but i resolved by using $project and achieved the expected result. thanks

Collectives™ on Stack Overflow

Nested grouping in Mongodb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related