从 MongoDB 中的子文档聚合总计 [英] Aggregating a total from subdocuments in MongoDB

问题描述

我有一个像下面这样的文档，我基本上想为子文档中的项目生成一个聚合.

I have a document like the one below, I'd essentially like to produce an aggregate for the items in a sub document.

本质上，每个文档都是一个销售记录，其中包含销售的详细信息和一个包含每个已售商品数量的子文档/数组.

Essentially each document is a sales record, which has details of the sales and a sub document / array with the qtys of each item sold.

我想汇总所有已售商品.

I'd like to produce a summary of all the items sold.

所以一个示例集合是:

{
  non_relevant_1: "ABC",
  non_relevant_2: "DEF",
  items_array: {
    "item_1": 1,
    "item_2": 2,
    "item_3": 1,
    "item_4": 1
    }
},

{
  non_relevant_1: "HIJ",
  non_relevant_2: "KLM",
  items_array: {
    "item_1": 3,
    "item_2": 2,
    "item_3": 4
    }
}

然后我希望能够产生类似的东西:

I'd then like to be able to produce something like:

{
items_array: {
    "item_1": 4,
    "item_2": 4,
    "item_3": 5,
    "item_4": 1
    }
}

非常感谢.

推荐答案

我认为您需要更改架构，您正在将数据保存在键中.MongoDB 操作符不会有未知键，例如我们不能通过未知键进行分组.要做到这些，我们会做一些复杂而缓慢的事情，例如 $objectToArray.

I think you need to change your schema, you are saving data in keys. MongoDB operators are not made to have unknown keys, for example we can't group by an unknown key.To do those we do complicated and slow things like $objectToArray.

你想要作为结果的数据也有同样的问题.

Also the data that you want as results have the same problem.

如果您查看查询，则只需要中间的 $unwind 和 $group 需要它，具有更改的架构，并要求键中没有数据的数据.

If you look at the query only the middle $unwind and $group would be needed it, with a changed schema, and asking for data without data in keys.

我的意思是而不是

items_array: {
    "item_1": 1,
    "item_2": 2,
    "item_3": 1,
    "item_4": 1
    }

你的收藏应该是这样的(查询的第一部分会改变你的架构)

Your collection should have being like(first part of the query does that changing your schema)

items_array: [
    {"name" "item_1",
     "qty" : 1},
    {"name" "item_2",
     "qty" : 2},
    {"name" "item_3",
     "qty" : 1},
    {"name" "item_4",
     "qty" : 1}
    ]

此外，结果应该只有已知的密钥.也许你被卡住的原因是.你会让事情变得更容易.

Also the results should have known keys only. Maybe the reason you were stuck is that.You will make things much easier for you.

此处测试代码

查询(查询有效，适用于您的架构，但我告诉了您我的想法)

Query (query works, for your schema but i told you what i think)

db.collection.aggregate([
  {
    "$addFields": {
      "items_array": {
        "$map": {
          "input": {
            "$map": {
              "input": {
                "$objectToArray": "$items_array"
              },
              "as": "m",
              "in": [
                "$$m.k",
                "$$m.v"
              ]
            }
          },
          "as": "item",
          "in": {
            "name": {
              "$arrayElemAt": [
                "$$item",
                0
              ]
            },
            "qty": {
              "$arrayElemAt": [
                "$$item",
                1
              ]
            }
          }
        }
      }
    }
  },
  {
    "$unwind": {
      "path": "$items_array"
    }
  },
  {
    "$group": {
      "_id": "$items_array.name",
      "total-qty": {
        "$sum": "$items_array.qty"
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "items_array": {
        "$push": {
          "$map": {
            "input": {
              "$map": {
                "input": {
                  "$objectToArray": "$$ROOT"
                },
                "as": "m",
                "in": [
                  "$$m.k",
                  "$$m.v"
                ]
              }
            },
            "as": "i",
            "in": {
              "$arrayElemAt": [
                "$$i",
                1
              ]
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0
    }
  },
  {
    "$addFields": {
      "items_array": {
        "$arrayToObject": "$items_array"
      }
    }
  }
])

这篇关于从 MongoDB 中的子文档聚合总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！