How to do aggregation with sum in a Mongo query?

Question

I'm new to MongoDB and need to write this query. My JSON Request is like this. I need to build the query in mongodb with aggregation

JSON

[
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "LeadName": "Test",
      "status": "Lost",
      "lead_value": 78000
    }
  },
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "LeadName": "Trail",
      "status": "Trial",
      "lead_value": 75200
    }
  },
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "LeadName": "Teja",
      "status": "Customer",
      "lead_value": 45000
    }
  },
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "LeadName": "Kushbu",
      "status": "Trial",
      "lead_value": 20000
    }
  },
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "LeadName": "Krishna",
      "status": "Trial",
      "lead_value": 18000
    }
  },
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "LeadName": "Test1",
      "status": "Trial",
      "lead_value": 12000
    }
  },
  {
    "_id": {
      "user_id": "89",
      "name": "Prashanth Reddy",
      "LeadName": "Ganesh",
      "status": "Trial",
      "lead_value": 12000
    }
  },
  {
    "_id": {
      "user_id": "89",
      "name": "Prashanth Reddy",
      "LeadName": "Hima Sree",
      "status": "Customer",
      "lead_value": 1750
    }
  }
]

I want the response like sum of the all the "Lead_values" except "Customer" and count the Status also

[
  {
    "_id": {
      "user_id": "1",
      "name": "Madhu",
      "lead_value": 120354,
      "count" : 5
    }
  },
  {
    "_id": {
      "user_id": "89",
      "name": "Prashanth Reddy",
      "lead_value": 12000,
      "count" : 1
    }
  }
]

How to write in MongoDB Aggregation?

Answer 1

You just need a simple $group stage:

db.collection.aggregate([
    {
        $match: {
            "_id.status": {$ne: "Customer"}
        }
    },
    {
        $group: {
            _id: "$_id.user_id",
            lead_values: {$sum: "$_id.lead_value"},
            name: {$first: "$_id.name"},
            count: {$sum: 1}
        }
    }
])

You can now reformat the data as you wish.