我有两个不同的集合，一个用于 * emailtemplates *，另一个用于 * emails *。我尝试编写一个聚合管道，显示使用 * emailtemplates * 集合中的模板 * name * 发送每个模板的次数。我知道我可以在 * emailtemplates * 集合上执行一个管道，并按模板名称计数每个模板。但是这将排除从未发送过的模板。
下面是我整理的一些示例文档...
// Example emailtemplate documents:
[
{
"name": "WELCOME-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, Welcome to the club"
},
{
"name": "GOODBYE-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, were sorry to see you go"
},
{
"name": "YOURE-FIRED-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, Pack your stuff and go"
}
]
// Example email documents:
[
{
"templateName": "WELCOME-01",
"recipient": "john.doe@gmail.com",
"parameters": {
"firstName": "John",
"lastName": "Doe"
}
},
{
"templateName": "WELCOME-01",
"recipient": "david.chappelle@gmail.com",
"parameters": {
"firstName": "David",
"lastName": "Chappelle"
},
},
{
"templateName": "GOODBYE-01",
"recipient": "the.joker@gmail.com",
"parameters": {
"firstName": "The",
"lastName": "Joker"
}
}
]因此，您可以看到每个 * email * 文档的 * templateName * 值如何与每个 * emailtemplates * 文档的 * name * 相匹配。我尝试做的是从 * templateName * 集合中进行选择，并显示有多少 * email * 文档与之关联。
我知道如何使用 * localField * 和 * foreignField * 选项执行此操作：
db.notificationtemplates.aggregate([
{
$lookup:{
from: "notifications",
localField: "name",
foreignField: "templateName",
as: "notifications"
}
},
{
$project:{
_id: 0,
templateName: "$name",
amountSent: { $size: "$notifications"}
}
}
]);结果是：
[
{ templateName: 'WELCOME-01', amountSent: 2 },
{ templateName: 'GOODBYE-01', amountSent: 1 },
{ templateName: 'YOURE-FIRED-01', amountSent: 0 }
}这样就可以了，但是我需要给 *$lookup * 添加一些逻辑，这意味着我需要一个 *$pipeline *，这意味着我不能简单地使用 * localField * 和 * foreignField *，否则我会得到错误：
服务器错误：带有"pipeline"的$lookup不能指定"localField"或"foreignField"
下面是我到目前为止编写的查询，尝试做同样的事情：
db.emailtemplates.aggregate([
{ $match:{channel: 'email'} },
{
$lookup: {
from: "emails",
let: {
templateName: "$templateName",
name: "$name"
},
pipeline: [
{ $match: { $expr: {$eq: [ "$$templateName","$name"] } } },
{ $project:{
"templateName":"$templateName",
"name":"$name"
} }
],
as: "emails"
}
}
])下面是上述查询的结果：
[
{
"name": "WELCOME-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, Welcome to the club",
"emails":[
{
"templateName": "WELCOME-01"
},
{
"templateName": "WELCOME-01",
},
{
"templateName": "GOODBYE-01"
}
]
},
{
"name": "GOODBYE-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, were sorry to see you go",
"emails":[
{
"templateName": "WELCOME-01"
},
{
"templateName": "WELCOME-01",
},
{
"templateName": "GOODBYE-01"
}
]
},
{
"name": "YOURE-FIRED-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, Pack your stuff and go",
"emails":[
{
"templateName": "WELCOME-01"
},
{
"templateName": "WELCOME-01",
},
{
"templateName": "GOODBYE-01"
}
]
}
]- 注意**：我现在只输出 * templateName *，以便查看与 * emails * 值匹配的文档。
如果您查看每个输出文档的 * emails * 值，它不会只查找 * templateName * 与 * emailtemplate * 文档的本地 * name * 值匹配的电子邮件。
我希望看到的输出将是更多的东西沿着线：
[
{
"name": "WELCOME-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, Welcome to the club",
"emails":[
{
"templateName": "WELCOME-01"
},
{
"templateName": "WELCOME-01"
}
}
]
},
{
"name": "GOODBYE-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, were sorry to see you go",
"emails":[
{
"templateName": "GOODBYE-01"
}
]
},
{
"name": "YOURE-FIRED-01",
"channel": "email",
"status":"active",
"title": "Hello, welcome {firstname} {lastnane}",
"parameters":[{
"name": "firstname",
"type": "string",
"required": true
},{
"name": "lastname",
"type": "string",
"required": true
}],
"body":"Dear {firstname}, Pack your stuff and go",
"emails":[]
}
]
1条答案
按热度按时间2admgd591#
您实际上非常接近。您只是混淆了变量和字段名。注意，
$$表示MongoDB聚合管道中的变量。在$let子句中，您使用emailtemplates.name中的值创建变量name。因此，对于$lookup子管道，您应该比较$$name和$templateName。它在您查找emails集合时引用emails.templateName。正确的语法应该如下所示：
Mongo Playground