MongoDB Join Fields Aggregation-CodePudding

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...

CodePudding user response：

If two values are Equal make a Condition and change datatype after trying to concatenates save in Same array or use trim to four letter ',' and store Database then Fetch the movies value and this new Concatenate array. I am not sure This I just give my IdEA

CodePudding user response：

The _id for the group is $year, so every year will have a separate document.

What you should do is 2 group stages, the first with _id: "$year" to count the number of movies per year, and the second, with the _id: $movies with the addToSet to group years with the same number of movies

CodePudding user response：

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