Add total count to grouped + summed data

user_12925471 0
Viewed 374

I'm struggling with a MongoDb query.

I have a query that makes group and count some fields, for example:

field_name | count
___________________
 
field1     | 10
field2     | 20
field3     | 30

And also I would like to add total count at the end to make it look like this:

field_name | count
___________________
 
field1     | 10
field2     | 20
field3     | 30
total      | 60

I understand how I can achieve it in SQL (either UNION or ROLLUP) although I can't find a way to do it in Mongo.

Here's my query just in case:

db.subscriptions.aggregate([
{$match: {isExpired: false}},
{$group: {_id: '$productId', count: {$sum: 1}}}
])
mongodb-query mongodb
1 Answers

You can achieve this with $facet

$facet categorizes incoming documents. The approach I followed is, Get all document into one array (originalArr). You can use $exists:true for field_name or counts or both. It depends on you. Get the total count in another array (totalArr). Then $concat both array into one.

[
  {
    "$facet": {
      "originalArr": [
        {
          $match: {
            count: {
              "$exists": true
            }
          }
        },
        {
          "$project": {
            _id: 0
          }
        }
      ],
      "totalArr": [
        {
          "$group": {
            "_id": null,
            "field_name": {
              "$first": "total"
            },
            "count": {
              $sum: "$count"
            }
          }
        },
        {
          "$project": {
            _id: 0
          }
        }
      ]
    }
  },
  {
    $project: {
      combined: {
        "$concatArrays": [
          "$originalArr",
          "$totalArr"
        ]
      }
    }
  },
  {
    "$unwind": "$combined"
  },
  {
    "$replaceRoot": {
      "newRoot": "$combined"
    }
  }
]

Working Mongo playground

user_79757710
Related