我正在学习MongoDB聚合,并一直在与下面的问题来回。我有两个集合,建模如下:
Contacts
{
_id: ObjectId('2341908342'),
fName: Test,
lName: TestTwo,
companyId: "61f4147b8415abfc04d09207",
},
{
_id: ObjectId('2341908342'),
fName: John,
lName: Doe,
companyId: "62847a16ee5c5047820415d2",
}
Companies
{
_id: ObjectId('61f4147b8415abfc04d09207'),
companyName: "Facebook",
domain: "www.facebook.com",
}
{
_id: ObjectId('62847a16ee5c5047820415d2'),
companyName: "Google",
domain: "www.Google.com",
}
我正试图编写一个聚合函数,该函数将获取所有联系人,然后在联系人和公司之间匹配companyId,并像这样投影数据:
Contacts
{
_id: ObjectId('2341908342'),
fName: Test,
lName: TestTwo,
companyId: "61f4147b8415abfc04d09207",
companyName: "Facebook",
domain: "www.facebook.com",
}
到目前为止,我在Typescript中是这样做的:
await allContactsCursor.forEach((contact: any) => {
if (contact.companyId) {
const company = allCompanies.find(
(co) => co._id.toString() === contact.companyId,
)
if (company) {
allContactsCursor.push({
...contact,
companyName: company.companyName,
domain: company.domain,
})
} else {
allContactsCursor.push(contact)
}
} else {
allContactsCursor.push(contact)
}
})
但是我想通过聚合来实现。我还在学习聚合,但这是我目前为止的成果:
try {
const allContactsCursor = await contactsCollection
.aggregate([
{
$match: {
orgId: {
$in: [new ObjectId(req.user.orgId)],
},
},
},
{
$lookup: {
from: "companies",
let: {
companyId: {
$toString: "$_id",
},
},
pipeline: [
{
$match: {
$expr: {
$eq: ["$companyId", "$$companyId"],
},
},
},
],
as: "companyName",
},
},
])
.toArray()
我知道这是不正确的,但我想我需要比较公司ID,然后在此之后编写$project以获得我的公司名称和域字段添加。然而,我不确定,甚至不确定这是否可能。谢谢你提供的任何帮助。
使用$unwind
来处理$lookup
的结果数组,使用$mergeObjects
和$replaceRoot
来处理结果到您期望的形式。
db.Contacts.aggregate([
{
"$lookup": {
"from": "Companies",
"let": {
companyId: "$companyId"
},
"pipeline": [
{
$match: {
$expr: {
$eq: [
"$$companyId",
{
$toString: "$_id"
}
]
}
}
}
],
"as": "companyLookup"
}
},
{
$unwind: "$companyLookup"
},
{
"$replaceRoot": {
"newRoot": {
"$mergeObjects": [
"$$ROOT",
{
companyId: "$companyLookup.companyId",
companyName: "$companyLookup.companyName"
}
]
}
}
},
{
$unset: "companyLookup"
}
])
Mongo操场边注:考虑重构模式以保持数据类型的一致性。也就是说,在两个集合中,companyId应该是ObjectId或string。