node.js - Mongodb查询多个集合

Question

我有两个收藏

用户

[{
    "id":1,
    "name":"a1",
    "emailAddress":"1@s.com",
},
{
    "id":2,
    "name":"a2",
    "emailAddress":"2@s.com",
},
{
    "id":3,
    "name":"a3",
    "emailAddress":"3@s.com",
}]

组织

[{
    "emailAddress": "1@s.com",
    "name" : "org1"
},
{
    "emailAddress": "2@s.com",,
    "name" : "org1"
},
{
    "emailAddress" : "3@s.com",
    "name" : "org2"
}]

现在我想获取组织 org1 的所有用户，如下所示

[{
    "id":1, "name":"a1", "emailAddress":"1@s.com","orgName" : "org1"
},
{
    "id":2, "name":"a2", "emailAddress":"2@s.com","orgName" : "org1"
}]

我已经检查了 debRef 和 Lookup，但它们在嵌套中返回

我怎样才能实现这个目标？

Answer 1

您可以使用 $lookup 简单地实现此目的和 $project聚合

如果您有 mongodb 版本 3.6 及更高版本

db.users.aggregate([
  { "$lookup": {
    "from": Organisation.collection.name,
    "let": { "emaildid": "$emaildid" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$emaildid", "$$emaildid" ] } } }
    ],
    "as": "organisation"
  }},
  { "$unwind": "$organisation" },
  { "$project": {
    { "id": 1, "name": 1, "emailid": 1, "org": "$organisation.org1" }
  }}
])

如果您有 mongodb 版本 3.4 及更低版本

db.users.aggregate([
  { "$lookup": {
    "from": Organisation.collection.name,
    "localField": "emaildid",
    "foreignField": "emaildid",
    "as": "organisation"
  }},
  { "$unwind": "$organisation" },
  { "$project": {
    { "id": 1, "name": 1, "emailid": 1, "org": "$organisation.org1" }
  }}
])

尝试使用$replaceRoot还有

db.Organisation.aggregate([
  { "$match": { "name": "org1" }},
  { "$lookup": {
    "from": Organisation.collection.name,
    "let": { "emailAddress": "$emailAddress", "name": "$name" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$emailAddress", "$$emailAddress" ] } } },
      { "$addFields": { "orgName": "$$name" }}
    ],
    "as": "users"
  }},
  { "$unwind": "$users" },
  { "$replaceRoot": { "newRoot": "$users" } }
])