mongodb if field has given value in one collection then search corresponding field in another collections and return combined values from many collec

huangapple go评论59阅读模式
英文:

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"
    }
  }
])

Playground


<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>



huangapple
  • 本文由 发表于 2023年4月17日 16:13:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/76033004.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定