MongoDB Aggregation:双重查找,并将查找响应合并到各个对象 [英] MongoDB Aggregation : Double lookup, and merge lookup response to respective object
问题描述
我正在尝试进行汇总,但是找不到合适的管道来完成它.
I'm trying an aggregation but I can't find the right pipeline to do it.
所以,这是我的文档模型的一部分:
So, this is a part of my document model :
//company.js
{
"_id" : "5dg8aa8c435b1e2868c841f6",
"name" : "My Corp",
"externalId" : "d7f348c9-c69b-69c4-923c-91458c53dc22",
"professionals_customers" : [
{
"company" : "6f4d01eb3b948150c2aad9c0"
},
{
"company" : "5dg7aa8c366b1e2868c841f6",
"contact" : "5df8ab5c355b1e2999c841f7"
}
],
}
我试图返回专业客户领域,其中充满了数据,就像传统的填充一样.
I try to return the professionnal customers fields hydrated with data, like a classic populate would do.
公司字段来自公司集合,联系方式由用户集合提供
Company field came from the company collection and contact is provided by the user collection
所需的输出必须类似于:
The desired output must look like :
{
"professionals_customers" : [
{
"company": {
"_id": "6f4d01eb3b948150c2aad9c0",
"name": "Transtar",
"externalId": "d7f386c9-c79b-49c5-905c-90750c42dc22",
},
},
{
"company": {
"_id": "5dg7aa8c366b1e2868c841f6",
"name": "Aperture",
"externalId": "d7f386c9-c69b-49c4-905c-90750c53dc22",
},
"contact" : {
"_id": "5df8ab5c355b1e2999c841f7",
"firstname": "Caroline",
"lastname": "Glados",
"externalId": "d7f386c9-c69b-49c4-905c-90750c53dc22", //same externalId as above, the user belongs to the company
},
}
]
}
目前,我已经尝试了多种解决方案,但仍达不到目标.
At this point I've tried multiple solutions but I can't reach my goal.
let query = [{
$match : { _id : companyId }
},{
$lookup : {
from: 'companies',
localField : 'professionals_customers.company',
foreignField : '_id',
as : 'professionalsCustomers'
}
},{
$lookup : {
from: 'users',
localField : 'professionals_customers.contact',
foreignField : '_id',
as : 'contacts'
}
}]
在这一点上,我已经有了两个包含所有必需信息的新阵列,但是我不知道如何将正确的联系人分组到正确的公司.另外,也许尝试使用$ lookup填充数据以保留初始结构要比尝试通过共享的externalId重新组合professionalCustomers和联系人要容易.
At this, point I' ve got two new arrays with all the needed informations, but I don't know how to get the right contact grouped with the right company. Also, maybe it's easier to try to populate the data (with $lookup) keeping the initial struct than trying to regroup professionalCustomers and contacts through the shared externalId.
其他信息:
-属于公司的用户具有相同的externalId.
-An user that belongs to a company has the same externalId.
-我不想使用经典的填充,在那之后,我需要执行其他一些操作
-I don't want to use a classical populate, after that, I need to do some other operations
推荐答案
尝试以下查询:
db.companies.aggregate([
{ $match: { _id: companyId } },
{ $unwind: "$professionals_customers" },
{
$lookup: {
from: "companies",
localField: "professionals_customers.company",
foreignField: "_id",
as: "professionals_customers.company"
}
},
{
$lookup: {
from: "users",
localField: "professionals_customers.contact",
foreignField: "_id",
as: "professionals_customers.contact"
}
},
{
$addFields: {
"professionals_customers.company": {
$arrayElemAt: ["$professionals_customers.company", 0]
},
"professionals_customers.contact": {
$arrayElemAt: ["$professionals_customers.contact", 0]
}
}
},
{
$group: { _id: "$_id", professionals_customers: { $push: "$professionals_customers" }, data: { $first: "$$ROOT" } }
},
{ $addFields: { "data.professionals_customers": "$professionals_customers" } },
{ $replaceRoot: { newRoot: "$data" } }
])
注意::如果需要,您需要将类型为 string 的字段/输入转换为 ObjectId().基本的事情是您需要检查两个要比较的字段的类型,或者是否要与数据库中的输入字段匹配.
Note : If needed you need to convert fields/input which is of type string to ObjectId(). Basic thing is you need to check types of two fields being compared or input-to-field-in-DB matches or not.
这篇关于MongoDB Aggregation:双重查找,并将查找响应合并到各个对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!