Advanced Data Aggregation with MongoDB and Python

I have a task involving the aggregation and processing of data stored in a MongoDB collection named flight_data. This collection holds a wealth of information about flights, encompassing details such as flight numbers, departure airports, arrival airports, fuel consumption, and flight distances.

Here’s a glimpse of the document structure within the flight_data collection:

{
    "_id": ObjectId("3f94fb7faff0b71ae5e3cb8d"),
    "flight_number": "SWA4146",
    "departure_airport": "SNA",
    "arrival_airport": "SMF",
    "fuel_consumption_gallons": 700.4,
    "flight_distance_miles": 450
}

I’m trying to find a way to calculate the total fuel consumption for each unique flight route, and I aim to identify the routes that have consumed the most fuel. My desired output format is:

{
    "route": "SNA-SMF",
    "total_fuel_consumption_gallons": THE_CALCULATED_AMOUNT
},
{
    "route": "SFO-ORD",
    "total_fuel_consumption_gallons": THE_CALCULATED_AMOUNT
}

Here’s my Python script:

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db = client["aviation_data"]
collection = db["flight_data"]

pipeline = [
    {
        "$group": {
            "_id": {
                "departure_airport": "$departure_airport",
                "arrival_airport": "$arrival_airport"
            },
            "total_fuel_consumption_gallons": {
                "$sum": "$fuel_consumption_gallons"
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "route": {
                "$concat": ["$_id.departure_airport", "-", "$_id.arrival_airport"]
            },
            "total_fuel_consumption_gallons": 1
        }
    },
    {
        "$sort": {
            "total_fuel_consumption_gallons": -1
        }
    },
    {
        "$limit": 5
    }
]

result = list(collection.aggregate(pipeline))
print(result)

However, when I execute the script, I encounter this error:

pymongo.errors.OperationFailure: The field 'departure_airport' must be an accumulator object

Does anyone know where the error is here? I’ve seen some similar questions, but I couldn’t fix my script using the answers that I found.

>Solution :

So, this error typically indicates that MongoDB aggregation framework is interpreting a field in an unexpected manner.

The problem lies in the $project stage where you attempt to concatenate the ‘departure_airport‘ and ‘arrival_airport‘ fields. To mitigate this error, you should reshape the pipeline as follows:

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db = client["aviation_data"]
collection = db["flight_data"]

pipeline = [
    {
        "$group": {
            "_id": {
                "departure_airport": "$departure_airport",
                "arrival_airport": "$arrival_airport"
            },
            "total_fuel_consumption_gallons": {
                "$sum": "$fuel_consumption_gallons"
            }
        }
    },
    {
        "$addFields": {
            "route": {
                "$concat": [
                    "$_id.departure_airport",
                    "-",
                    "$_id.arrival_airport"
                ]
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "route": 1,
            "total_fuel_consumption_gallons": 1
        }
    },
    {
        "$sort": {
            "total_fuel_consumption_gallons": -1
        }
    },
    {
        "$limit": 5
    }
]

result = list(collection.aggregate(pipeline))
print(result)

The $addFields stage is used to create the ‘route‘ field, eliminating the error and ensuring that you can achieve the intended results.