MongoDB Aggregation -查找邮政编码中的客户

Question

我有2个MongoDB文档:商店和客户

商店文档如下

[
{
id: 1,
zipcodes:[ 
'12345',
'11111',
]
},  
{
id:2,
zipcodes:[
'45678',
]
}
]

客户文档如下

[
{
id:20,
name: 'First Last',
address: [
{
id:110,
address: 'address line1',
zipcode: '12345',
},
{
id:111,
address: 'address new',
zipcode: '45678',
}
]
},
{
id:25,
name: 'First Last2',
address: [
{
id:113,
address: 'address new',
zipcode: '45678',
},
]
},
{
id:29,
name: 'First Name',
address: [
{
id:119,
address: 'address liner',
zipcode: '11111',
},
{
id:120,
address: 'address new2',
zipcode: '12345',
}
]
},
]

预期输出:我需要获得相同邮政编码的客户

[
{
zipcode: 12345,
customers:[
{id:20},
{id:29},
]
},
{
zipcode: 11111,
customers:[
{id:29},
]
},
{
zipcode: 45678,
customers:[
{id:25},
]
},
]

我试图解决这个使用MongoDB聚合框架，但没有运气到目前为止。我不知道如何匹配数组中的对象元素。提前感谢。请帮助。

Answer 1

1. $unwind-解构zipcodes数组到多个文档

2. $lookup-连接shops和customers。

   2.1。$match-shops'zipcode在address.zipcode和customers之间

   2.2。$project-装饰customers数组的输出文档。

3. $project-修饰输出文档。

db.shops.aggregate([
{
$unwind: "$zipcodes"
},
{
$lookup: {
from: "customers",
let: {
zipcodes: "$zipcodes"
},
pipeline: [
{
$match: {
$expr: {
"$in": [
"$$zipcodes",
"$address.zipcode"
]
}
}
},
{
$project: {
_id: 0,
id: 1
}
}
],
as: "customers"
}
},
{
$project: {
_id: 0,
zipcodes: 1,
customers: 1
}
}
])

Mongo Playground示例