时间戳

首页 > 解决方案 > 按天和项目总计分组，但输出项目名称作为键

javascript - 按天和项目总计分组，但输出项目名称作为键

问题描述

我一直在尝试这些示例： https://docs.mongodb.com/manual/reference/operator/aggregation/push/和 https://docs.mongodb.com/manual/reference/operator/aggregation/addToSet/

样本文件：

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }
{ "_id" : 6, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-15T12:05:10Z") }
{ "_id" : 7, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T14:12:12Z") }

但我需要的是它们的混合。在推送示例中，结果如下所示：

{
    "_id" : { "day" : 46, "year" : 2014 },
    "itemsSold" : [
        { "item" : "abc", "quantity" : 10 },
        { "item" : "xyz", "quantity" : 10 },
        { "item" : "xyz", "quantity" : 5 },
        { "item" : "xyz", "quantity" : 10 }
    ]
}
{
    "_id" : { "day" : 34, "year" : 2014 },
    "itemsSold" : [
        { "item" : "jkl", "quantity" : 1 },
        { "item" : "xyz", "quantity" : 5 }
    ]
 }
 {
     "_id" : { "day" : 1, "year" : 2014 },
     "itemsSold" : [ { "item" : "abc", "quantity" : 2 } ]
 }

在 $addToSet 示例中，结果如下所示：

{ "_id" : { "day" : 46, "year" : 2014 }, "itemsSold" : [ "xyz", "abc" ] }
{ "_id" : { "day" : 34, "year" : 2014 }, "itemsSold" : [ "xyz", "jkl" ] }
{ "_id" : { "day" : 1, "year" : 2014 }, "itemsSold" : [ "abc" ] }

我想要的是这样的：

{ "_id" : { "day" : 46, "year" : 2014 }, "itemsSold" : { "xyz": 25, "abc": 10 } }
{ "_id" : { "day" : 34, "year" : 2014 }, "itemsSold" : { "xyz": 5, "jkl": 1 ] }
{ "_id" : { "day" : 1, "year" : 2014 }, "itemsSold" : { "abc": 2 } }

这可能吗？如果是的话，任何指南，方向都会有所帮助。

标签： javascriptmongodbmongodb-queryaggregation-framework

解决方案

根据您的数据，您需要两个$group阶段，以便首先收集每个"item"然后将这些项目详细信息添加到数组中。

根据您可用的 MongoDB 版本，您将如何处理其余部分。对于 MongoDB 3.6（从 3.4.7 开始），您可以使用$arrayToObject它来重塑数据：

db.collection.aggregate([
  { "$group": {
    "_id": {
      "year": { "$year": "$date" },
      "dayOfYear": { "$dayOfYear": "$date" },
      "item": "$item"
    },
    "total": { "$sum": "$quantity" }
  }},
  { "$group": {
    "_id": {
      "year": "$_id.year",
      "dayOfYear": "$_id.dayOfYear"
    },
    "itemsSold": { "$push": { "k": "$_id.item", "v": "$total" } }
  }},
  { "$sort": { "_id": -1 } },
  { "$addFields": { 
    "itemsSold": { "$arrayToObject": "$itemsSold" }
  }}
])

或者使用早期版本，您可以简单地发布处理结果。无论如何，所有“聚合”工作都在最后阶段之前完成：

db.collection.aggregate([
  { "$group": {
    "_id": {
      "year": { "$year": "$date" },
      "dayOfYear": { "$dayOfYear": "$date" },
      "item": "$item"
    },
    "total": { "$sum": "$quantity" }
  }},
  { "$group": {
    "_id": {
      "year": "$_id.year",
      "dayOfYear": "$_id.dayOfYear"
    },
    "itemsSold": { "$push": { "k": "$_id.item", "v": "$total" } }
  }},
  { "$sort": { "_id": -1 } },
  /*
  { "$addFields": { 
    "itemsSold": { "$arrayToObject": "$itemsSold" }
  }}
  */
]).map( d => Object.assign( d, 
  { 
    itemsSold: d.itemsSold.reduce((acc,curr) =>
      Object.assign(acc, { [curr.k]: curr.v }),
      {}
    )
  }
))

无论哪种方式都会产生相同的预期结果：

{
        "_id" : {
                "year" : 2014,
                "dayOfYear" : 46
        },
        "itemsSold" : {
                "xyz" : 25,
                "abc" : 10
        }
}
{
        "_id" : {
                "year" : 2014,
                "dayOfYear" : 34
        },
        "itemsSold" : {
                "jkl" : 1,
                "xyz" : 5
        }
}
{
        "_id" : {
                "year" : 2014,
                "dayOfYear" : 1
        },
        "itemsSold" : {
                "abc" : 2
        }
}

因此，您可以使用新的聚合功能来做事，但实际上最终结果只是“重塑”，通常最好留给客户端处理。

推荐阅读