MongoDB Join Fields Aggregation

Question

In MongoDB, I have a collection of different movies with their years.

Consider these documents:

{
    "_id" : ObjectId("63a994974ac549c5ea982d2b"),
    "title" : "Destroyer",
    "year" : 1907
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d2a"),
    "title" : "Aquaman",
    "year" : 1902
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d29"),
    "title" : "On the Basis of Sex",
    "year" : 1907   
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d28"),
    "title" : "Holmes and Watson",
    "year" : 1902
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d27"),
    "title" : "Conundrum: Secrets Among Friends",
    "year" : 1902
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d26"),
    "title" : "Welcome to Marwen",
    "year" : 1907
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d25"),
    "title" : "Mary Poppins Returns",
    "year" : 1997
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d24"),
    "title" : "Bumblebee",
    "year" : 2004
}

I want to show the year or years with the fewest movies showing the number of movies from that year. So, with the previous documents, you can see there are 2 years with the same count of movies. Years: 1907 and 1902.

Therefore, I want to join those years in a single document. I tried this code:

var query1 = {$group: {"_id": "$year",
        "movies": {$sum:1},
        "Years": {$addToSet:"$year"},
}}


var stages = [query1]

db.movies.aggregate(stages)

However, the output is this:

{
    "_id" : 1907,
    "movies" : 3,
    "Years" : [ 1907 ]
},

{
    "_id" : 1902,
    "movies" : 3,
    "Years" : [ 1902 ]
},

I do not want that. The expect output that I want is this:

{
    "_id" : 1902,
    "movies" : 3,
    "Years" : [ 1907, 1902 ]
}

Once you get that, what I want to show as a final output is this:

{
    "_id" : [1907, 1902],
    "movies" : 3
}

I do not know how to do that. I cannot join all these years in an array...

How can I get that? How can I obtain the previous output?

Thanks so much for your attention. Whatever you need, ask it pls...

Answer 1

One option is to do what @Joe suggested:

db.collection.aggregate([
  {$group: {
      _id: "$year",
      count: {$sum: 1}
  }},
  {$group: {
      _id: "$count",
      years: {$push: "$_id"}
  }},
  {$sort: {_id: -1}},
  {$limit: 1},
  {$project: {_id: 0, count: "$_id", years: 1}}
])

See how it works on the playground example