英文:
mongodb if field has given value in one collection then search corresponding field in another collections and return combined values from many collec
问题
{ "accountCode": "xyz", "ITSM": "SNOW", "manager": "John", "admin": "Peter" },
{ "accountCode": "abc", "ITSM": "SNOW", "manager": "Lisa", "admin": "Mona" }
英文:
let's say I have 3 collections in MongoDB
"Col1": [
{
"accountCode": "xyz",
"ITSM": "SNOW",
"anotherFieldxxx": "x"
},
{
"accountCode": "abc",
"ITSM": "SNOW",
"anotherFieldxxx": "x"
}
],
"Col2": [
{
"accountCode": "xyz",
"manager": "John",
"anotherFieldyyy": "yyy"
},
{
"accountCode": "abc",
"manager": "Lisa",
"anotherFieldyyy": "yyy"
}
],
"Col3": [
{
"accountCode": "xyz",
"admin": "Peter",
"anotherFieldzzz": "z"
},
{
"accountCode": "abc",
"admin": "Mona",
"anotherFieldyyy": "yyy"
}
]
In the query I want to give only one value and it's "ITSM" : "SNOW"
If there is many documents in Col1
with "ITSM" : "SNOW"
then I want to search for "accountCode"
in Col2
and get "manager"
value, then search for the same "accountCode"
in Col3
and get "admin"
value. Finally I want to get output like:
{"accountCode" : "xyz",
"ITSM" : "SNOW",
"manager" : "John",
"admin" : "Peter"},
{"accountCode" : "abc",
"ITSM" : "SNOW",
"manager" : "Paul",
"admin" : "Stephen"}
I tried with Aggregation but I am just beginner in MongoDb and finally I gave up.
Can you suggest what I should use to get my result?
---update
I have such query
db.Col1.aggregate([
{$project: {"anotherFieldxxx": 0}},
{$match: {"accountCode": "xyz"}},
{$lookup: {
from: "Col2",
localField: "accountCode",
foreignField: "accountCode",
as: "Col2"
}},
{ $unwind: "$Col2" },
{ $project: { "Col2.anotherFieldyyy" : 0 }},
{ $match: { "accountCode" : "xyz"}},
{
$lookup: {
from: "Col3",
localField: "accountCode",
foreignField: "accountCode",
as: "Col3"
}},
{$unwind: "$Col3"},
{$project: {"Col2.anotherFieldzzz": 0}}
])Pretty()
but what if there are also documents with "accountCode" : "abc"
in all collections?
答案1
得分: 1
一种选项是使用$match
来匹配具有所需值的文档,然后使用它们的accountCode
进行$lookup
。无需限制搜索到一个特定的accountCode
:
db.Col1.aggregate([
{$match: {"ITSM": "SNOW"}},
{$lookup: {
from: "Col2",
localField: "accountCode",
foreignField: "accountCode",
pipeline: [{$project: {manager: 1, _id: 0}}],
as: "Res2"
}},
{$lookup: {
from: "Col3",
localField: "accountCode",
foreignField: "accountCode",
pipeline: [{$project: {admin: 1, _id: 0}}],
as: "Res3"
}},
{$project: {
_id: 0,
accountCode: 1,
ITSM: 1,
manager: {$first: "$Res2.manager"},
admin: {$first: "$Res3.admin"}
}}
])
在playground example上查看它的运行方式。
英文:
One option is to $match
documents with your wanted value and then use there accountCode
for $lookup
. No need to limit the search to one specific accountCode
:
db.Col1.aggregate([
{$match: {"ITSM": "SNOW"}},
{$lookup: {
from: "Col2",
localField: "accountCode",
foreignField: "accountCode",
pipeline: [{$project: {manager: 1, _id: 0}}],
as: "Res2"
}},
{$lookup: {
from: "Col3",
localField: "accountCode",
foreignField: "accountCode",
pipeline: [{$project: {admin: 1, _id: 0}}],
as: "Res3"
}},
{$project: {
_id: 0,
accountCode: 1,
ITSM: 1,
manager: {$first: "$Res2.manager"},
admin: {$first: "$Res3.admin"}
}}
])
See how it works on the playground example
答案2
得分: 0
db.Col1.aggregate([
{
$project: { "anotherFieldxxx": 0 }
},
{
$match: { "accountCode": "xyz" }
},
{
$lookup: {
from: "Col2",
localField: "accountCode",
foreignField: "accountCode",
as: "Res2"
}
},
{
$lookup: {
from: "Col3",
localField: "accountCode",
foreignField: "accountCode",
as: "Res3"
}
},
{ $unwind: "$Res2" },
{ $unwind: "$Res3" },
{
$project: {
accountCode: 1,
ITSM: 1,
manager: "$Res2.manager",
admin: "$Res3.admin"
}
}
])
<details>
<summary>英文:</summary>
db.Col1.aggregate([
{
$project: { "anotherFieldxxx": 0 }
},
{
$match: { "accountCode": "xyz" }
},
{
$lookup: {
from: "Col2",
localField: "accountCode",
foreignField: "accountCode",
as: "Res2"
}
},
{
$lookup: {
from: "Col3",
localField: "accountCode",
foreignField: "accountCode",
as: "Res3"
}
},
{ $unwind: "$Res2" },
{ $unwind: "$Res3" },
{
$project: {
accountCode: 1,
ITSM: 1,
manager: "$Res2.manager",
admin: "$Res3.admin"
}
}
])
[Playground](https://mongoplayground.net/p/lRA6HFPUjlS)
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论