Mongoose在聚合中使用子查询来汇总数据(Mongoose using subquery in aggregation to sum data)

我是猫鼬的新手。我有这样的模特。

var ForumSchema = new mongoose.Schema({ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Title : {type : String}, Content : {type : String}, Tags : [{type : String}], isPublic : {type : Boolean, default : false}, Vote : [{ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Kind : {type : Boolean} }], Rate : [{ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Count : {type : Number} }], Comment : [{ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Content : String, Created_at : {type : Date, required : true, default : Date.now} }], Created_at : {type : Date, required : true, default : Date.now}, Updated_at : {type : Date}

}）;

我希望得到论证数据与Vote总和，该值为真。喜欢这个json。

{ [ _id : <Some object id>, User_id : <Some object id from User Model>, Title : <Title>, Content : <Content>, Tags : [Some array], isPublic : true, UpVote : 23, .... .... .... ]

}

在mysql中，我可以使用子查询来完成此操作。我怎么能在猫鼬中做到这一点？

I am new in mongoose. I have model like this.

var ForumSchema = new mongoose.Schema({ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Title : {type : String}, Content : {type : String}, Tags : [{type : String}], isPublic : {type : Boolean, default : false}, Vote : [{ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Kind : {type : Boolean} }], Rate : [{ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Count : {type : Number} }], Comment : [{ User_id : {type : Schema.Types.ObjectId, ref : 'User'}, Content : String, Created_at : {type : Date, required : true, default : Date.now} }], Created_at : {type : Date, required : true, default : Date.now}, Updated_at : {type : Date}

});

I want to get Forum data with sum of Vote that value is true. Like this json.

{ [ _id : <Some object id>, User_id : <Some object id from User Model>, Title : <Title>, Content : <Content>, Tags : [Some array], isPublic : true, UpVote : 23, .... .... .... ]

}

In mysql I can do this by using subquery. How can I do it in mongoose?

最满意答案

使用MongoDB服务器3.4及更高版本，您可以运行使用$addFields运算符的聚合管道，该运算符在Vote数组上具有$filter ，以过滤那些Kind属性值匹配为true的元素，当您获得已过滤的数组时，使用它作为$size运算符的输入表达式，然后计算筛选数组中项的计数。

请考虑以下操作以获得所需的结果：

Forum.aggregate([ { "$addFields": { "UpVote": { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } } } } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

说明

在上面，内在表达

{ "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } }

选择要根据指定条件返回的数组子集。结果，它返回一个只包含与条件匹配的元素的数组。

input属性是指解析为数组的表达式。在上面，输入是Votes数组。

另一个字段表示输入数组中元素的变量名称。 as表达式通过此变量访问输入数组中的每个元素。

cond字段包含一个表达式，用于确定是否在结果数组中包含该元素。表达式通过as指定的变量名访问元素。

因此，在上面，如果被评估的数组中的元素具有等于true的Kind子属性，由表达式"$$el.Kind" ，则条件匹配并且元素包含在要返回的子集中。

举个简单的例子，举个例子来说这个高级表达式：

{ "$filter": { "input": [ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": false, "User_id": "58ad50a429b2961777f91c97" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ], "as": "el", "cond": { "$eq": ["$$el.Kind", true] } } }

返回数组

[ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ]

条件部分

"cond": { "$eq": ["$$el.Kind", true] }

可以简化为

"cond": "$$el.Kind"

因为"$$el.Kind"表达式已经计算为布尔值。

例如， $size运算符可以简单地计算数组中元素的数量，从而计算表达式

{ "$size": { "$filter": { "input": [ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": false, "User_id": "58ad50a429b2961777f91c97" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ], "as": "el", "cond": { "$eq": ["$$el.Kind", true] } } } }

表达为

{ "$size": [ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ] }

并以2作为计数返回结果。

对于相反的DownVote计数，同样的逻辑适用：

Forum.aggregate([ { "$addFields": { "UpVote": { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } } }, "DownVote": { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": { "$not": ["$$el.Kind"] } } } } } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

对于早期的MongoDB版本3.2，您需要预测文档中的每个其他元素：

Forum.aggregate([ { "$project": { "User_id" : 1, "Title" : 1, "Content" : 1, "Tags" : 1, "isPublic" : 1, "UpVote" : { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } } }, .... .... .... } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

对于不支持$filter运算符的版本，请使用$setDifference运算符，而不是：

Forum.aggregate([ { "$project": { "User_id" : 1, "Title" : 1, "Content" : 1, "Tags" : 1, "isPublic" : 1, "UpVote" : { "$size": { "$setDifference": [ { "$map": { "input": "$Vote", "as": "el", "in": { "$cond": ["$$el.Kind", "$$el", false] } }}, [false] ] } }, .... .... .... } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

With MongoDB server 3.4 and above, you can run an aggregate pipeline that uses the $addFields operator which has a $filter on the Vote array to filter those element that have a Kind property value matching true and when you get the filtered array, use it as an input expression for the $size operator which then calculates the count of the items in the filtered array.

Consider the following operation to get the desired result:

Forum.aggregate([ { "$addFields": { "UpVote": { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } } } } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

Explanations

In the above, the inner expression

{ "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } }

selects a subset of the array to return based on the specified condition. As a result, it returns an array with only those elements that match the condition.

The input property refers to an expression that resolves to an array. In the above, the input is the Votes array.

The other field as represents the variable name for the element in the input array. The as expression accesses each element in the input array by this variable.

The cond field holds an expression that determines whether to include the element in the resulting array. The expression accesses the element by the variable name specified in as.

So in the above if the element in the array being evaluated has the Kind subproperty equal to true, denoted by the expression "$$el.Kind", then the condition is matched and the element is included in the subset to be returned.

As a simple example, take for instance this high-level expression:

{ "$filter": { "input": [ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": false, "User_id": "58ad50a429b2961777f91c97" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ], "as": "el", "cond": { "$eq": ["$$el.Kind", true] } } }

returns the array

[ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ]

The conditional part

"cond": { "$eq": ["$$el.Kind", true] }

can be simplified to just

"cond": "$$el.Kind"

as "$$el.Kind" expression already evaluates to a boolean.

The $size operator trivially calculates the number of elements in an array, thus the expression, for example

{ "$size": { "$filter": { "input": [ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": false, "User_id": "58ad50a429b2961777f91c97" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ], "as": "el", "cond": { "$eq": ["$$el.Kind", true] } } } }

is expressed as

{ "$size": [ { "Kind": true, "User_id": "58afed97bc343887a9ac9206" }, { "Kind": true, "User_id": "58b3f0f598501abacd8ff391" } ] }

and return a result with 2 as count.

For the contrary DownVote count, the same logic applies:

Forum.aggregate([ { "$addFields": { "UpVote": { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } } }, "DownVote": { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": { "$not": ["$$el.Kind"] } } } } } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

For the earlier MongoDB version 3.2, you will need to project each and every other element in the document:

Forum.aggregate([ { "$project": { "User_id" : 1, "Title" : 1, "Content" : 1, "Tags" : 1, "isPublic" : 1, "UpVote" : { "$size": { "$filter": { "input": "$Vote", "as": "el", "cond": "$$el.Kind" } } }, .... .... .... } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

For versions that do not support the $filter operator, use the $setDifference operator instead as:

Forum.aggregate([ { "$project": { "User_id" : 1, "Title" : 1, "Content" : 1, "Tags" : 1, "isPublic" : 1, "UpVote" : { "$size": { "$setDifference": [ { "$map": { "input": "$Vote", "as": "el", "in": { "$cond": ["$$el.Kind", "$$el", false] } }}, [false] ] } }, .... .... .... } } ]).exec((err, results) => { if (err) throw err; console.log(results); })

更多推荐