Aggregate two groups on same collection

Working with Data
6

This is the kind of thing I was thinking of, obviously will need a supporting index for the initial match stage and be wary of data volumes that hit the $facet as they cannot make use of indexes:

db.getCollection("token_balances").aggregate([
{
    $match:{
        $or:[
            {"token_bought_address":"0xBB"},
            {"token_sold_address":"0xBB"}
        ]
    }
},
{
    $facet:{
        sold:[
            {
                $match:{
                    "token_sold_address":"0xBB"
                }
            },
            {
                $group:{
                    _id:'$maker',
                    total:{$sum:{$multiply:[-1, '$token_sold_amount']}}
                }
            }
        ],
        bought:[
            {
                $match:{
                    "token_bought_address":"0xBB"
                }
            },
            {
                $group:{
                    _id:'$taker',
                    total:{$sum:'$token_bought_amount'}
                }
            }
        ],
    }
},
{
    $project:{
        allItem:{
            $setUnion:["$sold","$bought"]
        }
    }
},
{
    $unwind:"$allItem"
},
{
    $replaceRoot:{
        newRoot:"$allItem"
    }
},
{
    $group:{
        _id:'$_id',
        total:{$sum:"$total"}
    }
},
{
    $project:{
        _id:0,
        "wallet":"$_id",
        "amount":"$total"
    }
}
])

With you data, I pushed it into a local MariaDB instance:
image
and ran your query on it:

with token_balances as (
    select -- tokens sold
        -sum(token_sold_amount) as amount
        , maker as wallet
    from trades
    where token_sold_address = '0xBB'
    group by 2
    
    union all
    
    select -- tokens bought
        sum(token_bought_amount) as amount
        , taker as wallet
    from trades
    where token_bought_address = '0xBB'
    group by 2
)

select
	wallet
        , sum(amount) as amount
    from token_balances
    group by 1

Which gave this:
image

The Mongo equivalent gave:
image

You could eliminate the $repalceRoot stage from the aggregation pipeline as it’s just to simplify the next stage.

I obviously don’t have your dataset, but have a play with volumes and see how it performs on your data.

1 Like