Merging multiple aggregation queries to one with MongoDB

Question

I'm using these three queries to can have a python dataframe format with the columns : 'Date', '% part of business 2', '% part of business 3'. (for each day to have the percentage of gain from business 2 and 3).

query_business2 = collection.aggregate( [
        {
          '$match': {'Business': 2}      
        },
        {
                '$group': {
                        '_id': '$Date',
                        'stab2': {'$sum': '$Money'}
                }
        },
        {
                '$sort': {'_id': 1}
        }
        ])
query_business3 = collection.aggregate([
        {
                '$match': {'Business':3}
        },
        {
                '$group': {
                        '_id': '$Date',
                        'stab3': {'$sum': '$Money'}
                        }
        },
        {
                '$sort': {'_id': 1}
        }
        ])
query_total = collection.aggregate([
        {
                '$group': {
                        '_id': '$Date',
                        'total': {'$sum': '$Money'}
                        }
        },
        {
                '$sort': {'_id': 1}
        }
        ])

For this to be faster, I would like to merge these three queries into one. I tried using '$or' but didn't work for unashable dict. Is there a better way to do that ? It might be possible to directly make the dataframe format without using pandas after this queries and to calculate directly the percentage of each business compared to the total money earned. Thank you for your help

Here are couple of posts with similar question and answers: Aggregate and sum by one key and rest and Partition data around a match query during aggregation. — prasad_
– prasad_, Commented May 4, 2020 at 9:09
Thank you, it was hard for me to find precise help about this problem but with $cond I will probably be able to do it. — Kazen
– Kazen, Commented May 4, 2020 at 9:18

Kazen · Accepted Answer · 2020-05-04 09:57:59Z

1

Thanks to prasad_ the answer is :

query_business = collection.aggregate([
        {
            '$group':{
                    '_id': '$Date',
                    'total_2': {'$sum' : {'$cond': [{'$eq': ['$Business', 2]}, '$Money', 0]}},
                    'total_3': {'$sum' : {'$cond': [{'$eq': ['$Business', 3]}, '$Money', 0]}},
                    'total': {'$sum': '$Money'},
                    }
        },
        {
            '$match': {'$and': [{ 'total_2': {'$gt': 0}}, {'total': {'$gt': 0}},{'total_3':{'$gt':0}}]}     
        },
        {
            '$addFields':{
                    'part_2': { "$multiply": [ { "$divide": ["$total_2","$total"] }, 100 ] },
                    'part_3': { "$multiply": [{'$divide': ['$total_3','$total']}, 100]}
                    }
        }
        ])

answered May 4, 2020 at 9:57

Kazen

213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merging multiple aggregation queries to one with MongoDB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related