chris
Reputation: 107
How to combine multiple aggregate query in a mongodb
I want to find an easy way to combine 3 ratings together for a mongoDB query. Specifically, is there some way to combine 3 aggregate calls in one. So for example i have the below data. Which lists ratings for movies, either in imbd.rating, tomato.rating or metacritic, I'd like to find the top 2 movies for each of these ratings. The bottom query is how i'm fetching the top 2 results based on the tomato.rating
data
{
"title": "Once Upon a Time in the West",
"year": 1968,
"rated": "PG-13",
"runtime": 175,
"countries": [
"Italy",
"USA",
"Spain"
],
"genres": [
"Western"
],
"director": "Sergio Leone",
"writers": [
"Sergio Donati",
"Sergio Leone",
"Dario Argento",
"Bernardo Bertolucci",
"Sergio Leone"
],
"actors": [
"Claudia Cardinale",
"Henry Fonda",
"Jason Robards",
"Charles Bronson"
],
"plot": "Epic story of a mysterious stranger with a harmonica who joins forces with a notorious desperado to protect a beautiful widow from a ruthless assassin working for the railroad.",
"poster": "http://ia.media-imdb.com/images/M/MV5BMTEyODQzNDkzNjVeQTJeQWpwZ15BbWU4MDgyODk1NDEx._V1_SX300.jpg",
"imdb": {
"id": "tt0064116",
"rating": 8.6,
"votes": 201283
},
"tomato": {
"meter": 98,
"image": "certified",
"rating": 9,
"reviews": 54,
"fresh": 53,
"consensus": "A landmark Sergio Leone spaghetti western masterpiece featuring a classic Morricone score.",
"userMeter": 95,
"userRating": 4.3,
"userReviews": 64006
},
"metacritic": 80,
"awards": {
"wins": 4,
"nominations": 5,
"text": "4 wins \u0026 5 nominations."
},
"type": "movie"
},
{
"title": "A Million Ways to Die in the West",
"year": 2014,
"rated": "R",
"runtime": 116,
"countries": [
"USA"
],
"genres": [
"Comedy",
"Western"
],
"director": "Seth MacFarlane",
"writers": [
"Seth MacFarlane",
"Alec Sulkin",
"Wellesley Wild"
],
"actors": [
"Seth MacFarlane",
"Charlize Theron",
"Amanda Seyfried",
"Liam Neeson"
],
"plot": "As a cowardly farmer begins to fall for the mysterious new woman in town, he must put his new-found courage to the test when her husband, a notorious gun-slinger, announces his arrival.",
"poster": "http://ia.media-imdb.com/images/M/MV5BMTQ0NDcyNjg0MV5BMl5BanBnXkFtZTgwMzk4NTA4MTE@._V1_SX300.jpg",
"imdb": {
"id": "tt2557490",
"rating": 6.1,
"votes": 126592
},
"tomato": {
"meter": 33,
"image": "rotten",
"rating": 4.9,
"reviews": 188,
"fresh": 62,
"consensus": "While it offers a few laughs and boasts a talented cast, Seth MacFarlane's overlong, aimless A Million Ways to Die in the West is a disappointingly scattershot affair.",
"userMeter": 40,
"userRating": 3,
"userReviews": 62945
},
"metacritic": 44,
"awards": {
"wins": 0,
"nominations": 6,
"text": "6 nominations."
},
"type": "movie"
},
{
"title": "Wild Wild West",
"year": 1999,
"rated": "PG-13",
"runtime": 106,
"countries": [
"USA"
],
"genres": [
"Action",
"Western",
"Comedy"
],
"director": "Barry Sonnenfeld",
"writers": [
"Jim Thomas",
"John Thomas",
"S.S. Wilson",
"Brent Maddock",
"Jeffrey Price",
"Peter S. Seaman"
],
"actors": [
"Will Smith",
"Kevin Kline",
"Kenneth Branagh",
"Salma Hayek"
],
"plot": "The two best hired guns in the West must save President Grant from the clutches of a nineteenth-century inventor-villain.",
"poster": "http://ia.media-imdb.com/images/M/MV5BNDI5NDk5MjgxMl5BMl5BanBnXkFtZTcwNjI1MTUyMQ@@._V1_SX300.jpg",
"imdb": {
"id": "tt0120891",
"rating": 4.8,
"votes": 124558
},
"tomato": {
"meter": 17,
"image": "rotten",
"rating": 4.1,
"reviews": 130,
"fresh": 22,
"consensus": "Bombastic, manic, and largely laugh-free, Wild Wild West is a bizarre misfire in which greater care was lavished upon the special effects than on the script.",
"userMeter": 28,
"userRating": 2.7,
"userReviews": 463740
},
"metacritic": 38,
"awards": {
"wins": 10,
"nominations": 11,
"text": "10 wins \u0026 11 nominations."
},
"type": "movie"
},
{
"title": "West Side Story",
"year": 1961,
"rated": "UNRATED",
"runtime": 152,
"countries": [
"USA"
],
"genres": [
"Crime",
"Drama",
"Musical"
],
"director": "Jerome Robbins, Robert Wise",
"writers": [
"Ernest Lehman",
"Arthur Laurents",
"Jerome Robbins"
],
"actors": [
"Natalie Wood",
"Richard Beymer",
"Russ Tamblyn",
"Rita Moreno"
],
"plot": "Two youngsters from rival New York City gangs fall in love, but tensions between their respective friends build toward tragedy.",
"poster": "http://ia.media-imdb.com/images/M/MV5BMTM0NDAxOTI5MF5BMl5BanBnXkFtZTcwNjI4Mjg3NA@@._V1_SX300.jpg",
"imdb": {
"id": "tt0055614",
"rating": 7.6,
"votes": 67824
},
"awards": {
"wins": 18,
"nominations": 11,
"text": "Won 10 Oscars. Another 18 wins \u0026 11 nominations."
},
"type": "movie"
},
{
"title": "Slow West",
"year": 2015,
"rated": "R",
"runtime": 84,
"countries": [
"UK",
"New Zealand"
],
"genres": [
"Action",
"Thriller",
"Western"
],
"director": "John Maclean",
"writers": [
"John Maclean"
],
"actors": [
"Kodi Smit-McPhee",
"Michael Fassbender",
"Ben Mendelsohn",
"Aorere Paki"
],
"plot": "A young Scottish man travels across America in pursuit of the woman he loves, attracting the attention of an outlaw who is willing to serve as a guide.",
"poster": "http://ia.media-imdb.com/images/M/MV5BNTYxNDA5ODk5NF5BMl5BanBnXkFtZTgwNzMwMzIwNTE@._V1_SX300.jpg",
"imdb": {
"id": "tt3205376",
"rating": 7,
"votes": 19101
},
"tomato": {
"meter": 92,
"image": "certified",
"rating": 7.5,
"reviews": 115,
"fresh": 106,
"consensus": "Slow West serves as an impressive calling card for first-time writer-director John M. Maclean -- and offers an inventive treat for fans of the Western.",
"userMeter": 75,
"userRating": 3.7,
"userReviews": 9850
},
"metacritic": 72,
"awards": {
"wins": 2,
"nominations": 9,
"text": "2 wins \u0026 9 nominations."
},
"type": "movie"
},
{
"title": "An American Tail: Fievel Goes West",
"year": 1991,
"rated": "G",
"runtime": 75,
"countries": [
"USA"
],
"genres": [
"Animation",
"Adventure",
"Family"
],
"director": "Phil Nibbelink, Simon Wells",
"writers": [
"Flint Dille",
"Charles Swenson",
"David Kirschner"
],
"actors": [
"Phillip Glasser",
"James Stewart",
"Erica Yohn",
"Cathy Cavadini"
],
"plot": "A family of Emigre mice decide to move out to the west, unaware that they are falling into a trap perpetrated by a smooth talking cat.",
"poster": "http://ia.media-imdb.com/images/M/MV5BMTYzODMxNTUyNF5BMl5BanBnXkFtZTcwMTAwMzQyMQ@@._V1_SX300.jpg",
"imdb": {
"id": "tt0101329",
"rating": 6.4,
"votes": 16013
},
"awards": {
"wins": 0,
"nominations": 0,
"text": ""
},
"type": "movie"
},
tomato.rating get
db.MovieData.aggregate([
{ "$unwind": "$tomato" },
{ "$sort": { "tomato.meter": -1 } },
{ "$limit": 2 },
{"$project": {title:1, "tomato.meter":1}},
])
imbd
db.MovieData.aggregate([
{ "$sort": { "imdb.rating": -1 } },
{ "$limit": 2 },
{"$project": {title:1, "imdb.rating":1}},
])
Meta critic
db.MovieData.aggregate([
{ "$sort": { "metacritic": -1 } },
{ "$limit": 2 },
{"$project": {title:1, "metacritic":1}},
])
Upvotes: 0
Views: 501
Answers (1)
Neil Lunn
Reputation: 151072
These sort of "limitted" operations are perfectly suited to $facet:
db.MovieData.aggregate([
{ "$facet": {
"tomato": [
{ "$sort": { "tomato.meter": -1 } },
{ "$limit": 2 },
{"$project": {title:1, "tomato.meter":1}},
],
"imdb": [
{ "$sort": { "imdb.rating": -1 } },
{ "$limit": 2 },
{"$project": {title:1, "imdb.rating":1}},
],
"metacrtic": [
{ "$sort": { "metacritic": -1 } },
{ "$limit": 2 },
{"$project": {title:1, "metacritic":1}},
]
}}
])
Returns from the data in the question:
{
"tomato" : [
{
"_id" : ObjectId("5c80979542dd84d91e5547d9"),
"title" : "Once Upon a Time in the West",
"tomato" : {
"meter" : 98
}
},
{
"_id" : ObjectId("5c80979542dd84d91e5547dd"),
"title" : "Slow West",
"tomato" : {
"meter" : 92
}
}
],
"imdb" : [
{
"_id" : ObjectId("5c80979542dd84d91e5547d9"),
"title" : "Once Upon a Time in the West",
"imdb" : {
"rating" : 8.6
}
},
{
"_id" : ObjectId("5c80979542dd84d91e5547dc"),
"title" : "West Side Story",
"imdb" : {
"rating" : 7.6
}
}
],
"metacrtic" : [
{
"_id" : ObjectId("5c80979542dd84d91e5547d9"),
"title" : "Once Upon a Time in the West",
"metacritic" : 80
},
{
"_id" : ObjectId("5c80979542dd84d91e5547dd"),
"title" : "Slow West",
"metacritic" : 72
}
]
}
As long as the results are "small" then it's perfectly okay to run multiple pipelines this way.
If results are large it would be better to run independent queries and preferably in parallel ( i.e under Promise.all() for NodeJS for example ) and combine results ( as the Promise.all() example would ).
Whilst this effectively requires more database connections, that additional overhead is typically negated by the parallel processing. And of course means combined results ( especially when processed from cursors ) would not cause the BSON limit on a response to be broken.
NOTE:
tomatois anObjectand not anArray, so you don't use$unwind
Upvotes: 1
Related Questions
- MongoDB - How do I add multiple aggregations in query
- Mongodb combine aggregate queries
- How to combine results in a Mongo aggregation query
- mongodb multiple aggregations in single operation
- mongodb multiple aggregates in same query, each with total
- Mongo Aggregate query - combine two AND queries
- Multiple Aggregate Query MongoDB
- MongoDB aggregation - how to preform multiple operations in single query?
- Aggregate Query in MongoDB
- Multiple aggregates in the same query