MongoDB Filter子数组取3个条件

Question

Mongo Playground

我有一个这样的集合:

[
{
"ProjectId": 123,
"FeedbackClient": [
{ "SentOn": "2021-01-01", "Grade": 2, "DateVoted": "2021-01-01" },
{ "SentOn": "2021-01-02", "Grade": 5, "DateVoted": "2021-02-01" },
{ "SentOn": "2021-01-03", "Grade": 2, "DateVoted": "2021-03-05" },
{ "SentOn": "2021-01-04", "Grade": 5, "DateVoted": "2021-04-01" },
{ "SentOn": "2021-01-05", "Grade": null, "DateVoted": null },
{ "SentOn": "2021-01-06", "Grade": null, "DateVoted": null }
],
"FeedbackFinal": { "Grade": 4, "DateVoted": "2021-01-01" }
},
{
"ProjectId": 456,
"FeedbackClient": [
{ "SentOn": "2021-01-01", "Grade": 2, "DateVoted": "2021-01-01" },
{ "SentOn": "2021-01-02", "Grade": 2, "DateVoted": "2021-02-01" },
{ "SentOn": "2021-01-03", "Grade": 5, "DateVoted": "2021-03-05" },
{ "SentOn": "2021-01-04", "Grade": 1, "DateVoted": "2021-04-01" },
{ "SentOn": "2021-01-05", "Grade": null, "DateVoted": null },
{ "SentOn": "2021-01-06", "Grade": null, "DateVoted": null }
],
"FeedbackFinal": null
}
]

我需要实现这个简单的逻辑:

1. 如果"FeedbackFinal"不为空，抓取它的Grade
2. 如果"FeedbackFinal"是空的，我需要"FeedbackClient"最近3个投票等级的平均年龄(datevvoted not null and sort DESC)

我可以更接近于做聚合& &;像这样的项目:

{
Grade: { $cond: { if: { $ne: [ "$FeedbackFinal", null ] }, 
then: "$FeedbackFinal.Grade", 
else: { $avg: "$FeedbackClient.Grade"} } }
}

但这里我做的是所有的AVG，而不仅仅是datevvotes的最后3个DESC

正确的做法是什么?地图,滤镜，然后AVG?只把datevvoted NOT null推到另一个道具，然后对它们进行AVG ?

Answer 1

解决方案#1:

db.feedbacks.aggregate([
{ $unwind: "$FeedbackClient" },
{
$sort: {
"FeedbackClient.DateVoted": -1
}
},
{
$group: {
_id: "$_id",
ProjectId: { $first: "$ProjectId" },
FeedbackClient: { $push: "$FeedbackClient" },
FeedbackFinal: { $first: "$FeedbackFinal" }
}
},
{
$addFields: {
grade: {
$cond: {
if: { $ifNull: ["$FeedbackFinal", true] },
then: { $avg: { $slice: ["$FeedbackClient.Grade", 3] } },
else: "$FeedbackFinal.Grade"
}
}
}
}
])

解决方案#2:如果使用MongoDB版本4.4+

db.feedbacks.aggregate([
{
$set: {
fbcForAvg: {
$function: {
body: function(FeedbackClient) {
FeedbackClient.sort((a, b) => {
return new Date(b.DateVoted).getTime() - new Date(a.DateVoted).getTime()
});

return FeedbackClient.slice(0, 3);
},
args: ["$FeedbackClient"],
lang: "js"
}
}
}
},
{
$addFields: {
grade: {
$cond: {
if: { $ifNull: ["$FeedbackFinal", true] },
then: { $avg: "$fbcForAvg.Grade" },
else: "$FeedbackFinal.Grade"
}
}
}
},
{
$project: { "fbcForAvg": 0 }
}
]);

输出:

/* 1 createdAt:3/8/2021, 5:15:51 PM*/
{
"_id" : ObjectId("60460e6fa4a6a12690d3fd8a"),
"ProjectId" : 123,
"FeedbackClient" : [
{
"SentOn" : "2021-01-04",
"Grade" : 5,
"DateVoted" : "2021-04-01"
},
{
"SentOn" : "2021-01-03",
"Grade" : 2,
"DateVoted" : "2021-03-05"
},
{
"SentOn" : "2021-01-02",
"Grade" : 5,
"DateVoted" : "2021-02-01"
},
{
"SentOn" : "2021-01-01",
"Grade" : 2,
"DateVoted" : "2021-01-01"
},
{
"SentOn" : "2021-01-05",
"Grade" : null,
"DateVoted" : null
},
{
"SentOn" : "2021-01-06",
"Grade" : null,
"DateVoted" : null
}
],
"FeedbackFinal" : {
"Grade" : 4,
"DateVoted" : "2021-01-01"
},
"grade" : 4
},
/* 2 createdAt:3/8/2021, 5:15:51 PM*/
{
"_id" : ObjectId("60460e6fa4a6a12690d3fd8b"),
"ProjectId" : 456,
"FeedbackClient" : [
{
"SentOn" : "2021-01-04",
"Grade" : 1,
"DateVoted" : "2021-04-01"
},
{
"SentOn" : "2021-01-03",
"Grade" : 5,
"DateVoted" : "2021-03-05"
},
{
"SentOn" : "2021-01-02",
"Grade" : 2,
"DateVoted" : "2021-02-01"
},
{
"SentOn" : "2021-01-01",
"Grade" : 2,
"DateVoted" : "2021-01-01"
},
{
"SentOn" : "2021-01-05",
"Grade" : null,
"DateVoted" : null
},
{
"SentOn" : "2021-01-06",
"Grade" : null,
"DateVoted" : null
}
],
"FeedbackFinal" : null,
"grade" : 2.6666666666666665
}

Answer 2

• $filter迭代FeedbackClient循环并过滤DateVoted不为null的元素
• $slice从上面过滤的元素中切片最后3个元素
• $let声明一个名为client的变量，以上动作的结果将在client中，在Grade
中得到以上client的过滤数组的平均值

db.collection.aggregate([
{
"$project": {
"ProjectId": 5.0,
Grade: {
$cond: {
if: { $ne: ["$FeedbackFinal", null] },
then: "$FeedbackFinal.Grade",
else: {
$let: {
vars: {
client: {
$slice: [
{
$filter: {
input: "$FeedbackClient",
cond: { $ne: ["$$this.DateVoted", null] }
}
},
-3
]
}
},
in: { $avg: "$$client.Grade" }
}
}
}
}
}
}
])

游乐场