CODEWITHSUNDEEP
mongodb
chris
chris

Reputation: 107

Finding the highest value from a field

I have the following object in a mongodb. I'm wanting to find the genre that has won the highest number of awards, and out of the whole collection find the top 3 most found genres. I'm not really sure how to go about targeting specific fields within a collection like this, is it better to treat it as a large array? or is that a ridiculous comment.

Tried query which fails because the genre field is not an accumulator 

db.MovieData.aggregate([
{$sort:{"awards.wins":-1}},
{$group:{"genres":"$genres"}}
])

Example data, there is far more data but i have limited it to 2 insertions 

[
    {
        "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"
    }

Upvotes: 0

Views: 56

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151180

What you are looking for is:

db.MovieData.aggregate([
  { "$unwind": "$genres" },
  { "$group": {
    "_id": "$genres",
    "totalWins": { "$sum": "$awards.wins" }
  }},
  { "$sort": { "totalWins": -1 } },
  { "$limit": 3 }
])

In short:

  • $unwind - The genres field is an array, you need that "flattened" in order to use as a "grouping key" for the next stage:

  • $group - Requires an _id which is the "grouping key" or the value that things are accumulated for. Though not a requirement this is typically paired with accumulators, which perform the "aggregation operations" such as $sum on a supplied field value. Here you want:

    { "$sum": "$awards.wins" }

    to accumulate that field.

  • $sort - Orders those results by the supplied field(s). In this case on the accumulated totalWins and in descending ( -1 ) order.
  • $limit - Is the number of result documents to limit the return to.

A good place to look for common examples is the SQL to Aggregation Mapping Chart in the core documentation, particularly if you have some working knowledge of SQL or even if you do not as general examples.

All of the Aggregation Pipeline Stages as well as the Aggregation Pipeline Operators also have various usage examples within their own documentation pages as well. Familiarizing yourself with these is useful in understanding how they apply to different problems

Upvotes: 2

Related Questions