How to calculate the amount with conditional?-CodePudding

I have such documents in MongoDB:

{
    "_id":{
        "$oid":"614e0f8fb2f4d8ea534b2ccb"
    },
    "userEmail":"[email protected]",
    "customId":"abc1",
    "amountIn":10,
    "amountOut":0,
    "createdTimestamp":1632505743,
    "message":"",
    "status":"ERROR",
}

The amountOut field can be 0, or a positive numeric value. I need to calculate the sum of the amountIn and amountOut fields only if it is positive.
At the moment I am doing it like this:

query = {
    'createdTimestamp': {'$gte': 1632430800},
    'createdTimestamp': {'$lte': 1632517200}
}
records = db.RecordModel.objects(__raw__=query).all()

total_amount = 0
for record in records:
    if record.amountOut > 0:
        total_amount  = record.amountOut
    else:
        total_amount  = record.amountIn

But this is very slow.
I know mongoengine has a sum method:

total_amount = db.PaymentModel.objects(__raw__=query).sum('amountIn')

But I don't know how to use the condition for this method.
Maybe there are some other ways to calculate the amount with the condition I need faster?

CodePudding user response：

You can use mongoengine's aggregation api which just allows you to execute aggregations normally.

Now you can use this pipeline in code which utilizes $cond:

query = {
    'createdTimestamp': {'$gte': 1632430800, '$lte': 1632517200},
}
pipeline = [
    {"$match": query},
    {
        "$group": {
            "_id": None,
            "total_amount": {
                "$sum": {
                    "$cond": [
                        {
                            "$gt": [
                                "$amountOut",
                                0
                            ]
                        },
                        "$amountOut",
                        "$amountIn"
                    ]
                }
            }
        }
    }
]

records = db.RecordModel.objects().aggregate(pipeline)

Mongo Playground