CODEWITHSUNDEEP
mongodbaggregation-framework
Jean Dupont
Jean Dupont

Reputation: 177

Aggregate result in mongoDB with calculation

I'm working under MongoDb database and I have this collection :

What I have ?

{
Name : "David",
Type : "First",
Score : 1.66 
}

{
Name : "David",
Type : "Second",
Score : 0.66 
}

{
Name : "Naim",
Type : "First",
Score : 0.33 
}

{
Name : "Naim",
Type : "Second",
Score : 0.10 
}

{
Name : "Joe",
Type : "First",
Score : 1.10 
}
{
Name : "Joe",
Type : "Second",
Score : 2 
}

What I want ?

I want to aggregate result in this way :

{
Name : "David",
Type : "First",
Score : 1.66 / ( 0.33 + 1.10 ) // first scores for other documents
}

{
Name : "David",
Type : "Second",
Score : 0.66 / ( 0.10 + 2 ) // second scores for other documents
}

And the same for other Names ...

Please How can I achieve the above ?

Upvotes: 1

Views: 541

Answers (1)

profesor79
profesor79

Reputation: 9473

As this looks like an easy solution for sql world (where we have CTE expressions), then using mongo we could utilise $lookup phase to simulate CTE.

First we calculate sum per test group, then join results on source, and using some arithmetic operations in $project phase, we calculate result.

db.dp.aggregate([{
            $group : {
                _id : "$Type",
                "SumScore" : {
                    $sum : "$Score"
                }
            }
        }, {
            $lookup : {

                from : "dp",
                localField : "_id",
                foreignField : "Type",
                as : "lookUp"
            }
        }, {
            $unwind : "$lookUp"
        }, {
            $project : {
                _id : 0,
                "Name" : "$lookUp.Name",
                "Type" : "$_id",
                "Score" : {
                    $divide : ["$lookUp.Score", {
                            $subtract : ["$SumScore", "$lookUp.Score"]
                        }
                    ]
                }
            }
        }
    ])

and the output:

{
    "Name" : "David",
    "Type" : "First",
    "Score" : 1.16083916083916
}

{
    "Name" : "Naim",
    "Type" : "First",
    "Score" : 0.119565217391304
}

{
    "Name" : "Joe",
    "Type" : "First",
    "Score" : 0.552763819095477
}

{
    "Name" : "David",
    "Type" : "Second",
    "Score" : 0.314285714285714
}

{
    "Name" : "Naim",
    "Type" : "Second",
    "Score" : 0.037593984962406
}

{
    "Name" : "Joe",
    "Type" : "Second",
    "Score" : 2.63157894736842
}

Upvotes: 1

Related Questions