Is it possible to aggregate $$ROOT in mongo db?

user_15264629 0
Viewed 553

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 :

  • I am hoping to get this result in one aggregation query.
  • So I am playing with $$ROOT.
  • In this path, now I am stuck with grouping the $$ROOT (to get sum of clicks for queries).
  • Can someone guide me a better path to do this?
aggregation-framework mongodb
1 Answers

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

user_19279910
Related