I have following Mongo Collection.
[
{
"query": "a",
"page": "p1",
"clicks": 10,
"date": "x"
},
{
"query": "b",
"page": "p1",
"clicks": 5,
"date": "x"
},
{
"query": "a",
"page": "p1",
"clicks": 5,
"date": "y"
},
{
"query": "c",
"page": "p2",
"clicks": 2,
"date": "y"
},
]
Output Should be like this :
[
{
"page" : "p1",
"most_clicks_query" : "a",
"sum_of_clicks_for_query" : 15
},
{
"page" : "p2",
"most_clicks_query" : "c",
"sum_of_clicks_for_query" : 2
},
]
Logic to get this Output :
I need the query name that has most clicks for each page with sum of clicks (for that query)
What I ask :
Here is the aggregation you're looking for:
db.collection.aggregate([
{
"$group": {
"_id": {
"page": "$page",
"query": "$query"
},
"sum_of_clicks_for_query": {
"$sum": "$clicks"
}
}
},
{
"$project": {
"_id": false,
"page": "$_id.page",
"most_clicks_query": "$_id.query",
"sum_of_clicks_for_query": true
}
},
{
$sort: {
"sum_of_clicks_for_query": -1
}
},
{
$group: {
_id: "$page",
group: {
$first: "$$ROOT"
}
}
},
{
$replaceRoot: {
newRoot: "$group"
}
}
])
Playground: https://mongoplayground.net/p/Uzk3CuSwVRM