How to aggregate the sum for each distinct record on a given condition and get the result in an array in mongodb

Question

I am trying to pass data to sparkline graph in Angular to show records with status "Open" for distinct SerialNumbers, but i am unable to push the results to an array.

Below query works but returns multiple documents, i want to have a single array with results.

**

db.Test.aggregate([{ $match: { status: 'Open' } },
{$group:{ _id: "$serialNumber" ,value: { $sum: {$sum: 1}}}}, 
{$group: {_id: "$_id",value:{$push:"$value"}}}])

**

For the below JSON data, how can i get result like "value:[3,2,1] or value:[{224:3},{221:2},{220:1}]". Where 3 is the total number of records where the status is Open for serialNumber=224, 2 is the sum of records where status is open for serialNumber=221, and 1 is the sum of records where status is open for serialNumber=220.

JSON DATA:

{ "_id" : 11, status:"Open", "serialNumber" : "224", },

{ "_id" : 12, status:"Open", "serialNumber" : "224", },

{ "_id" : 11, status:"Open", "serialNumber" : "224", },

{ "_id" : 11, status:"Closed", "serialNumber" : "224", },

{ "_id" : 11, status:"Open", "serialNumber" : "221", },

{ "_id" : 11, status:"Open", "serialNumber" : "220", },

I am new to Mongo and need help, Hope the question is understood. Thank you.

please provide a sample of your expected result. Do you need only an array of count, or must they be associated with corresponding serialNumber too? — matthPen
Thank you for the reply @matthPen. Need an array of count with corresponding serialNumber. For example: [{224:3},{221:2},{220:1}] — StackD

matthPen matthPen · Accepted Answer · 2018-10-05T09:19:05

Your expected result : [{224:3},{221:2},{220:1}] : i strongly discourage to have values as field name : if you get as result a document (or array of documents) with the following structure : result = {"224":3}, you have to know the field name to access to value (result.224 = 3).

My advice : use the following and access to result.results field to get your array (it will always retrun a unique document with results array)

db.collection.aggregate([
  {
    $match: {
      status: "Open"
    }
  },
  {
    $group: {
      _id: "$serialNumber",
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: null,
      results: {
        $push: {
          serialNumber: "$_id",
          count: "$count"
        }
      }
    }
  },
])

will return :

{
    "_id": null,
    "results": [
      {
        "count": 1,
        "serialNumber": "220"
      },
      {
        "count": 2,
        "serialNumber": "221"
      },
      {
        "count": 3,
        "serialNumber": "224"
      }
    ]
  }

How to aggregate the sum for each distinct record on a given condition and get the result in an array in mongodb

1 Answers