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