时间戳

首页 > 解决方案 > Mongo Aggregation $group by _id 和 date

node.js - Mongo Aggregation $group by _id 和 date

问题描述

我有一个 mongo 聚合查询，它当前基于位置 id 对结果进行分组，并使用 $push 使用数组中元素的 $size 返回计数值，以获取匹配给定日期的结果文档。我需要调整按位置 id 分组的每个结果的输出，并 $sum 按日期分组的计数值。下面的例子。

当前结果输出类似于：

[{ 
    "_id" : "100", 
    "appts" : [
        {
            "count" : NumberInt(2), 
            "date" : "2020-08-07"
        }, 
        {
            "count" : NumberInt(2), 
            "date" : "2020-08-07"
        } ]
},
 { 
    "_id" : "103", 
    "appts" : [
        {
            "count" : NumberInt(1), 
            "date" : "2020-08-07"
        }, 
        {
            "count" : NumberInt(3), 
            "date" : "2020-08-07"
        },
        {
            "count" : NumberInt(2), 
            "date" : "2020-08-08"
        } ]
}]

我正在尝试产生以下输出：

[{ 
    "_id" : "100", 
    "appts" : [
        {
            "count" : NumberInt(4), 
            "date" : "2020-08-07"
        } ]
},
 { 
    "_id" : "103", 
    "appts" :  [
        {
            "count" : NumberInt(4), 
            "date" : "2020-08-07"
        }, 
        {
            "count" : NumberInt(2), 
            "date" : "2020-08-08"
        } ]
}]

我目前的查询：

[
    {  $match: { ... } },
    {
        $group: {
            _id: {
                date: { $dateToString: { format: '%Y-%m-%d', date: '$time' } },
                loc: '$location.branchId',
                additionalReminders: '$analytics.twilio.additionalReminders'
            }
        }
    },
    {
        $group: {
            _id: '$_id.loc',
            appts: {
                $push: {
                    count: { $size: '$_id.additionalReminders' },
                    date: '$_id.date'
                }
            }
        }
    }
]

解决方案：将@Rfroes87 提供的以下查询添加到管道末尾解决了该问题。

{ $unwind: "$appts" },
{
    $group: {
        _id: { "id": "$_id", "date": "$appts.date" },
        "count": { $sum: "$appts.count" }
    }
},
{
    $group: {
        _id: "$_id.id",
        "appts": {  $push: { "count": "$count", "date": "$_id.date" }  }
    } 
}

标签： node.jsmongodbmongooseaggregation-framework

解决方案

也许您可以尝试，不确定您的文档结构，

预测的文档结构：

[{"time":ISODate("1970-01-01T00:00:00Z"),"location":{branchId:1},analytics:{twilio:{additionalReminders:[1,2,3]}}},{"time":ISODate("1970-01-01T00:00:00Z"),"location":{branchId:1},analytics:{twilio:{additionalReminders:[1,2]}}},{"time":ISODate("1970-01-02T00:00:00Z"),"location":{branchId:1},analytics:{twilio:{additionalReminders:[1,2,3]}}},]

db.collection.aggregate([
  {
    $group: {
      _id: {
        date: { $dateToString: { format: "%Y-%m-%d", date: "$time" } },
        loc: "$location.branchId"
      },
      // get size of array and sum here
      additionalReminders: {
        $sum: { $size: "$analytics.twilio.additionalReminders" }
      }
    }
  },
  {
    $group: {
      _id: "$_id.loc",
      appts: {
        $push: {
          // add just field here
          count: "$additionalReminders",
          date: "$_id.date"
        }
      }
    }
  }
])

操场

推荐阅读