mongodb如果字段在一个集合中有给定的值，则在另一个集合中搜索相应的字段，并返回来自多个集合的组合值

af7jpaap 于 2023-04-20 发布在 Go

关注(0)|答案(2)|浏览(127)

假设我在MongoDB中有3个集合

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

在查询中，我只想给予一个值，它是"ITSM" : "SNOW"如果在Col1中有很多文档带有"ITSM" : "SNOW"，那么我想在Col2中搜索"accountCode"并得到"manager"值，然后在Col3中搜索相同的"accountCode"并得到"admin"值。最后我想得到这样的输出：

{"accountCode" : "xyz",
"ITSM" : "SNOW",
"manager" : "John",
"admin" : "Peter"},
{"accountCode" : "abc",
"ITSM" : "SNOW",
"manager" : "Paul",
"admin" : "Stephen"}

我尝试了聚合，但我只是MongoDb的初学者，最后我放弃了。你能建议我应该用什么来得到我的结果吗？
---更新
我有这样的疑问

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

但是如果所有集合中也有"accountCode" : "abc"的文档呢？

mongodb

来源：https://stackoverflow.com/questions/76033004/mongodb-if-field-has-given-value-in-one-collection-then-search-corresponding-fie

2条答案

按热度按时间

bvjxkvbb1#

一个选项是$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上的工作原理

赞(0）回复(0）举报 2023-04-20

qacovj5a2#

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

赞(0）回复(0）举报 2023-04-20

我来回答

mongodb如果字段在一个集合中有给定的值，则在另一个集合中搜索相应的字段，并返回来自多个集合的组合值

2条答案

相关问题

热门标签

最新问答