如何在给定条件下聚合每个不同记录的总和并在 mongodb 中的数组中获取结果 [英] How to aggregate the sum for each distinct record on a given condition and get the result in an array in mongodb

问题描述

我正在尝试将数据传递到 Angular 中的迷你图，以显示不同序列号的状态为打开"的记录，但我无法将结果推送到数组.

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"}}}])

**

对于下面的 JSON 数据，我怎样才能得到像 "value:[3,2,1] 或 value:[{224:3},{221:2},{220:1}]".其中3是serialNumber=224状态为Open的记录总数，2是serialNumber=221状态是open的记录总和，1是serialNumber=220状态是open的记录总和.

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 数据:

{_id":11，状态:打开"，"serialNumber": "224",},

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

{_id":12，状态:打开"，"serialNumber": "224",},

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

{_id":11，状态:打开"，"serialNumber": "224",},

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

{_id":11，状态:关闭"，"serialNumber": "224",},

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

{_id":11，状态:打开"，"serialNumber": "221",},

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

{_id":11，状态:打开"，"serialNumber": "221",},

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

{_id":11，状态:打开"，"serialNumber": "220",},

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

我是 Mongo 的新手，需要帮助，希望问题得到理解.谢谢.

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

推荐答案

你的预期结果 : [{224:3},{221:2},{220:1}] : i strong不鼓励将值作为字段名称:如果您得到具有以下结构的文档(或文档数组):result = {"224":3}，您必须知道字段名称访问值(result.224 = 3).

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).

我的建议:使用 following 并访问 result.results 字段来获取您的数组(它将始终重新运行带有结果数组的唯一文档)

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"
        }
      }
    }
  },
])

将返回:

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

这篇关于如何在给定条件下聚合每个不同记录的总和并在 mongodb 中的数组中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！