MongoDB add grand total to sortByCount() in an aggregation pipeline-CodePudding

I have grouped all the users by country, but I would also like to have a row showing the grand total (users are tagged to a single country in our use case).

Data Model / Sample Input

The collection is filled with objects representing a country (name) and each contains a list of user objects in an array under users.

{ _id: ObjectId("..."),
  name: 'SG',
  type: 'COUNTRY',
  increment: 200,
  users: 
   [ ObjectId("..."),
     ObjectId("..."),
     ...

Query

db.collection.aggregate([{$match:{type:"COUNTRY"}},{$unwind:"$users"},{$sortByCount:"$name"}])

Current Results

{ _id: 'SG', count: 76 } 
{ _id: 'IN', count: 6 }  
{ _id: 'US', count: 4 }  
{ _id: 'FR', count: 3 }  
{ _id: 'UK', count: 2 } 
{ _id: 'RU', count: 1 } 
{ _id: 'CO', count: 1 } 
{ _id: 'DK', count: 1 } 
{ _id: 'ID', count: 1 } 
{ _id: 'PH', count: 1 }

Expected Results

{ _id: 'SG', count: 76 } 
{ _id: 'IN', count: 6 }  
{ _id: 'US', count: 4 }  
{ _id: 'FR', count: 3 }  
{ _id: 'UK', count: 2 } 
{ _id: 'RU', count: 1 } 
{ _id: 'CO', count: 1 } 
{ _id: 'DK', count: 1 } 
{ _id: 'ID', count: 1 } 
{ _id: 'PH', count: 1 }
{ _id: null, count: 96 } <<< TOTAL COUNT ADDED

Any tips to achieve this without resorting to complex or dirty tricks?

CodePudding user response：

I recommend just doing this in memory as the alternative is "hacky" but in order to achieve this in Mongo you just need to group all documents, add a new documents and unwind again, like so:

db.collection.aggregate([
  {
    $group: {
      _id: null,
      roots: {
        $push: "$$ROOT"
      },
      sum: {
        $sum: "$count"
      }
    }
  },
  {
    $addFields: {
      roots: {
        "$concatArrays": [
          "$roots",
          [
            {
              _id: null,
              count: "$sum"
            }
          ]
        ]
      }
    }
  },
  {
    $unwind: "$roots"
  },
  {
    $replaceRoot: {
      newRoot: "$roots"
    }
  }
])

Mongo Playground

CodePudding user response：

You can also try using $facet to calculate counts by country name and total count, and then combine them together. Something like this:

db.collection.aggregate([
  {
    $match: {
      type: "COUNTRY"
    }
  },
  {
    "$unwind": "$users"
  },
  {
    "$facet": {
      "groupCountByCountry": [
        {
          "$sortByCount": "$name"
        }
      ],
      "totalCount": [
        {
          "$group": {
            "_id": null,
            "count": {
              "$sum": 1
            }
          }
        }
      ]
    }
  },
  {
    "$project": {
      array: {
        "$concatArrays": [
          "$groupCountByCountry",
          "$totalCount"
        ]
      }
    }
  },
  {
    "$unwind": "$array"
  },
  {
    "$replaceRoot": {
      "newRoot": "$$ROOT.array"
    }
  }
])

Here's the playground link.