Home > Net >  mongodb does not return aggregate
mongodb does not return aggregate

Time:09-27

I have a movies database like below

{
    "docs": [
    {
    "_id": "1",
    "title": "Guardians of the Galaxy",
    "genre": "Action,Adventure,Sci-Fi",
    "Description": "A group of intergalactic criminals are forced to work together to stop a fanatical warrior from taking control of the universe.",
    "Director": "James Gunn",
    "Actors": "Chris Pratt, Vin Diesel, Bradley Cooper, Zoe Saldana",
     "year": 2014,
    "Runtime (Minutes)": 121,
    "rating": "G",
    "Votes": 757074,
    "Revenue (Millions)": 333.13,
    "Metascore": 76
  },
  {
     "_id": "2",
     "title": "Prometheus",
      "genre": "Adventure,Mystery,Sci-Fi",
    "Description": "Following clues to the origin of mankind, a team finds a structure on a distant moon, but they soon realize they are not alone.",
    "Director": "R_idley Scott",
    "Actors": "Noomi Rapace, Logan Marshall-Green, Michael Fassbender, Charlize Theron",
     "year": 2012,
    "Runtime (Minutes)": 124,
    "rating": "unrated",
    "Votes": 485820,
    "Revenue (Millions)": 126.46,
    "Metascore": 65
  },....

It is imported into mongodb

I need to answer this question "Write a mongodb query to find the year in which most number of movies were released"

> db.movies.aggregate([{$group:{_id:'$year',moviecount:{$sum:1}}}])
{ "_id" : null, "moviecount" : 1 }

This is the result I get. I suspect that I need to reference docs first and tell query that what I want to find is inside it?

Could someone reply? thanks.

APPEND EDIT:

I have included the first 3 entries in the database, there are in total 100 entries like below.

> db.movies.find()

> 
> { "_id" : ObjectId("6331285d6fd150e2880467f2"), "docs" : [ { "_id" :
> "1", "title" : "Guardians of the Galaxy", "genre" :
> "Action,Adventure,Sci-Fi", "Description" : "A group of intergalactic
> criminals are forced to work together to stop a fanatical warrior from
> taking control of the universe.", "Director" : "James Gunn", "Actors"
> : "Chris Pratt, Vin Diesel, Bradley Cooper, Zoe Saldana", "year" :
> 2014, "Runtime (Minutes)" : 121, "rating" : "G", "Votes" : 757074,
> "Revenue (Millions)" : 333.13, "Metascore" : 76 }, { "_id" : "2",
> "title" : "Prometheus", "genre" : "Adventure,Mystery,Sci-Fi",
> "Description" : "Following clues to the origin of mankind, a team
> finds a structure on a distant moon, but they soon realize they are
> not alone.", "Director" : "R_idley Scott", "Actors" : "Noomi Rapace,
> Logan Marshall-Green, Michael Fassbender, Charlize Theron", "year" :
> 2012, "Runtime (Minutes)" : 124, "rating" : "unrated", "Votes" :
> 485820, "Revenue (Millions)" : 126.46, "Metascore" : 65 }, { "_id" :
> "3", "title" : "Split", "genre" : "Horror,Thriller", "Description" :
> "Three girls are k_idnapped by a man with a diagnosed 23 distinct
> personalities. They must try to escape before the apparent emergence
> of a frightful new 24th.", "Director" : "M. Night Shyamalan", "Actors"
> : "James McAvoy, Anya Taylor-Joy, Haley Lu Richardson, Jessica Sula",
> "year" : 2016, "Runtime (Minutes)" : 117, "rating" : "unrated",
> "Votes" : 157606, "Revenue (Millions)" : 138.12, "Metascore" : 62 }, 
> ] }

when I try to use tthis command,

db.movies.aggregate([ {"$group":{"_id":"$year", "moviecount":{"$sum":1}}}, { "$sort":{"moviecount":1}}, { "$limit":1} ])

{ "_id" : null, "moviecount" : 1 }

CodePudding user response:

You have to unwind the docs array first and then reference year from the docs element, like this:

db.movies.aggregate(
[ 
  {$unwind: "$docs"}
  { "$group":
    {"_id":"$docs.year", "moviecount":{"$sum":1}}}, 
  { "$sort": {"moviecount":1} }, 
  { "$limit":1 } 
])
  • Related