从子文档中减去$ sum [英] Subtract $sum from Sub-document

问题描述

给出MongoDB表中的以下记录:

Given the following record in my MongoDB table:

{
    "_id" : ObjectId("5a00c1c71680084c55811ae2"),
    "name" : "test",
    "tenantId" : "paul",
    "price" : 300,
    "deposits" : [ 
        {
            "amount" : 100,
            "date" : ISODate("2017-11-07T14:08:19.324Z"),
            "_id" : ObjectId("5a01be55424b0f8922a5b472")
        },
        {
            "amount" : 50,
            "date" : ISODate("2017-11-87T14:08:19.324Z"),
            "_id" : ObjectId("5a01be55424b0f8922a5b473")
        }
    ],
    "attention" : "",
    "due" : ISODate("2017-10-26T22:00:00.000Z")
}

我想用特定的tenantId过滤所有记录，然后减去子文档中我的金额的总和.

I would like to filter all the records with a specific tenantId, and then subtract the SUM of my amounts in the subdocument.

我了解了如何汇总子文档:

I found out how to Sum the Subdocument:

db.table.aggregate( [
{ $match : { tenantId: "paul" } },
{ $unwind:{ path: "$deposits", preserveNullAndEmptyArrays: true  }},
{ $group: {
    _id: '$_id', 
    deposits: { $sum: '$deposits.amount' },

} } 

])； 但是当我尝试从

] ); but when i try to subtract the $sum from $price like

 deposits: { $subtract: [ $price , $sum: '$deposits.amount'  ] },

比我说错了

Error: Line 6: Unexpected token :

推荐答案

实际上，您可以轻松地做到:

Actually you can simply do:

db.table.aggregate( [
  { "$match" : { "tenantId": "paul" } },
  //{ $unwind:{ path: "$deposits", preserveNullAndEmptyArrays: true  }},
  { "$project":
    "deposits": { "$subtract": ["$price", { "$sum": "$deposits.amount" } ] }
  }}
])

从MongoDB 3.2开始，您实际上可以 $project 与 $sum 和一个参数数组(或一个数组)因此完全不需要$unwind.

Since MongoDB 3.2 you can actually $project with $sum and an array of arguments ( or an array ) and therefore do not need to $unwind at all.

在3.2版中进行了更改:$ sum在$ group和$ project阶段可用.在以前的MongoDB版本中，$ sum仅在$ group阶段可用.

在$ project阶段中使用时，$ sum返回每个文档的指定表达式或表达式列表的总和...

长"方式(即旧"方式)是实际使用 $project 跟随 $group :

The "long" way, which is the "old" way is to actually use $unwind, but you would then actually add a $project following the $group:

db.table.aggregate( [
  { "$match" : { "tenantId": "paul" } },
  { $unwind:{ path: "$deposits", preserveNullAndEmptyArrays: true  }},
  { "$group":
    "_id": "$_id",
    "price": { "$first": "$price" },
    "deposits": { "$sum": "$deposits.amount" }
  }},
  { "$project": {
    "deposits": { "$subtract": [ "$price", "$deposits" ] }
  }}
])

当然，您还需要 $first 累加器，以便从 $group 阶段，因此可以在随后的阶段中使用.

And of course you then need the $first accumulator in order to return the "price" field from the $group stage so it can be used in the following stage.

但是，如果您可以执行preserveNullAndEmptyArrays，那么您实际上拥有MongoDB 3.2，因此最好使用 without 语句

But if you can do preserveNullAndEmptyArrays, then you actually have MongoDB 3.2, and therefore are better off using the statement without the $unwind at all, since it's much faster to do it that way.

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