CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Slevin
Slevin

Reputation: 4222

How to get a nested group out of an aggregation

I have the following schema in my Mongo database:

[
  {
    departureAirport: "JFK",
    arrivalAirport: "FRA",
    departureDate: "2017-02-22T04:00:00.000Z",
    arrivalDate: "2017-02-22T09:45:00.000Z",
    flightNumber: "FOOBAR",
    availableSeats: 9,
    cheapestFare: 65.99
  }
]

There are a few thousand rows in this DB with different departure airports, arrival airports and dates.

Now I want to group the results to get the cheapest price per month per arrival airport like this:

[
  {
    arrivalAirport: "FRA",
    fares: [
     {'2017-02': 65.99},
     {'2017-03': 65.99}
    ]
  },
  {
    arrivalAirport: "JFK",
    fares: [
     {'2017-02': 65.99},
     {'2017-03': 65.99}
    ]
  }
]

Or even better:

[
  {
    arrivalAirport: "FRA",
    fares: {
     '2017-02': 65.99,
     '2017-03': 65.99
    }
  },
  {
    arrivalAirport: "JFK",
    fares: {
     '2017-02': 65.99,
     '2017-03': 65.99
    }
  }
]

This is how I tried it:

  Flight.aggregate([
    {
      $match: filter
    },
    {
      $group: {
        _id: {
          arrival: '$arrivalAirport'
        }
      }
    },
    {
      $group: {
        _id: {
          year: { $year: '$departureDate' },
          month: { $month: '$departureDate' }
        },
        cheapestFare: { $min: '$cheapestFare' }
      }
    },
    {
      $sort: {
        departureDate: 1
      }
    }
  ]

Obviously this can't work because I need all available months with their fares for each arrivalAirport and not just one group per arrivalAirport.

Next thing I've looked for: subqueries (I'm coming from SQL, so Mongo is fairly new to me). But it seems that MongoDB doesn't support something like subqueries (so I could create one subquery for each arrivalAirport). Are there any other options to get this to work without creating an extra query for each arrivalAirport?

Upvotes: 1

Views: 64

Answers (1)

chridam
chridam

Reputation: 103445

You could try running the following aggregate pipeline:

Flight.aggregate([
    {
        "$group": {
            "_id": {
                "arrivalAirport": "$arrivalAirport",
                "month": { "$dateToString": { "format": "%Y-%m", "date": "$arrivalDate" } }
            },
            "cheapestFare": { "$min": "$cheapestFare" }
        }
    },
    {
        "$group": {
            "_id": "$_id.arrivalAirport",
            "fares": {
                "$push": {
                    "date": "$_id.month",
                    "fare": "$cheapestFare"
                }
            }
        }       
    },
    {
        "$project": {
            "_id": 0,
            "arrivalAirport": "$_id",
            "fares": 1
        }
    }
], function(err, results) {
    if (err) throw err;
    console.log(results);
});

Upvotes: 1

Related Questions