猫鼬中的子文档总数 [英] Sum of subdocuments in Mongoose
问题描述
目前我有这个架构.
var cartSchema = new Schema({
userPurchased: {type: Schema.Types.ObjectId, ref: 'users'},
products: [
{
product: {type: Schema.Types.ObjectId, ref: 'products'},
size: {type: String, required: true},
quantity: {type: Number, required: true},
subTotal: {type: Number, required: true}
}
],
totalPrice: {type: Number, default: 0, required: true}
});
数据库记录示例
{
"_id": {
"$oid": "586f4be94d3e481378469a08"
},
"userPurchased": {
"$oid": "586dca1f5f4a7810fc890f97"
},
"totalPrice": 0,
"products": [
{
"product": {
"$oid": "58466e8e734d1d2b0ceeae00"
},
"size": "m",
"quantity": 5,
"subTotal": 1995,
"_id": {
"$oid": "586f4be94d3e481378469a09"
}
},
{
"subTotal": 1197,
"quantity": 3,
"size": "m",
"product": {
"$oid": "58466e8e734d1d2b0ceeae01"
},
"_id": {
"$oid": "586f4ef64d3e481378469a0a"
}
}
],
"__v": 0
}
是否可以将所有小计加起来并将其放入总价"字段中?目前，我正在考虑聚合函数，但我怀疑这是否是正确的方法.我想我需要同时更新查询和sum方法.有人可以帮我吗?
Is there any way to sum all the subTotal and put it in the total price field? Right now I am thinking about aggregate function but I doubt it will be the right approach in here. I guess I need an update query and sum method at the same time. Can anyone help me in here?
推荐答案
Using the aggregate() function, you can run the following pipeline to get the desired results:
var pipeline = [
{ "$unwind": "$products" },
{
"$group": {
"_id": "$_id",
"products": { "$push": "$products" },
"userPurchased": { "$first": "$userPurchased" },
"totalPrice": { "$sum": "$products.subTotal" }
}
}
]
Cart.aggregate(pipeline)
.exec(function(err, results){
if (err) throw err;
console.log(JSON.stringify(results, null, 4));
})
In the above pipeline, the first step is the $unwind operator
{ "$unwind": "$products" }
在将数据存储为数组时非常方便.将展开运算符应用于列表数据字段时，它将为应用展开的列表数据字段的每个元素生成一条新记录.它基本上使数据平坦化.
which comes in quite handy when the data is stored as an array. When the unwind operator is applied on a list data field, it will generate a new record for each and every element of the list data field on which unwind is applied. It basically flattens the data.
This is a necessary operation for the next pipeline stage, the $group step where you group the flattened documents by the _id field, thus effectively regrouping the denormalised documents back to their original schema.
$group 管道运算符类似于SQL的GROUP BY子句.在SQL中，除非使用任何聚合函数，否则不能使用GROUP BY.同样，您还必须在MongoDB中使用聚合函数(称为累加器).您可以在此处详细了解累加器.
The $group pipeline operator is similar to the SQL's GROUP BY clause. In SQL, you can't use GROUP BY unless you use any of the aggregation functions. The same way, you have to use an aggregation function in MongoDB (called accumulators) as well. You can read more about the accumulators here.
在此 $group 操作，计算totalPrice并返回原始字段的逻辑是通过
In this $group operation, the logic to calculate the totalPrice and returning the original fields is through the accumulators. You get thetotalPrice by summing up each individual subTotal values per group with $sum as:
"totalPrice": { "$sum": "$products.subTotal }
另一种表达方式
"userPurchased": { "$first": "$userPurchased" },
将使用 $first 为每个组的第一个文档返回一个userPurchased值.因此，可以有效地在 $unwind
will return a userPurchased value from the first document for each group using $first. Thus effectively rebuilding the original document schema before the $unwind
这里要注意的一件事是，在执行管道时，MongoDB通过管道将运算符相互传递.这里的管道"具有Linux的含义:运算符的输出成为后续运算符的输入.每个运算符的结果都是新的文档集合.因此，Mongo如下执行上述管道:
One thing to note here is when executing a pipeline, MongoDB pipes operators into each other. "Pipe" here takes the Linux meaning: the output of an operator becomes the input of the following operator. The result of each operator is a new collection of documents. So Mongo executes the above pipeline as follows:
collection | $unwind | $group => result
作为旁注，为了帮助您理解管道或在遇到意外结果时对其进行调试，请仅使用第一个管道运算符运行聚合.例如，在mongo shell中以以下方式运行聚合:
As a side note, to help with understanding the pipeline or to debug it should you get unexpected results, run the aggregation with just the first pipeline operator. For example, run the aggregation in mongo shell as:
db.cart.aggregate([
{ "$unwind": "$products" }
])
检查结果以查看products数组是否正确解构.如果可以达到预期的效果，请添加下一个:
Check the result to see if the products array is deconstructed properly. If that gives the expected result, add the next:
db.cart.aggregate([
{ "$unwind": "$products" },
{
"$group": {
"_id": "$_id",
"products": { "$push": "$products" },
"userPurchased": { "$first": "$userPurchased" },
"totalPrice": { "$sum": "$products.subTotal" }
}
}
])
重复这些步骤，直到完成最后一个管道步骤.
Repeat the steps till you get to the final pipeline step.
If you want to update the field then you can add the $out pipeline stage as the last step. This will write the resulting documents of the aggregation pipeline to the same collection, thus technically updating the collection.
var pipeline = [
{ "$unwind": "$products" },
{
"$group": {
"_id": "$_id",
"products": { "$push": "$products" },
"userPurchased": { "$first": "$userPurchased" },
"totalPrice": { "$sum": "$products.subTotal" }
}
},
{ "$out": "cart" } // write the results to the same underlying mongo collection
]
更新
要同时执行更新和查询，您可以在聚合回调中发出find()调用以获取更新的json，即
To do both the update and query, you could then issue a find() call in the aggregate callback to get the updated json i.e.
Cart.aggregate(pipeline)
.exec(function(err, results){
if (err) throw err;
Cart.find().exec(function(err, docs){
if (err) return handleError(err);
console.log(JSON.stringify(docs, null, 4));
})
})
使用承诺"，您可以选择以下方式替代
Using Promises, you could do this alternatively as
Cart.aggregate(pipeline).exec().then(function(res)
return Cart.find().exec();
).then(function(docs){
console.log(JSON.stringify(docs, null, 4));
});
这篇关于猫鼬中的子文档总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
