不与集合连接的查找

Question

我正在使用芒果游乐场加入两个集合，并期望结果如下：

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "id": 1,
    "name": "so1",
    "sop": [{
      "id": "sop1",
      "value": "sopValue"
    }],
    "sops": [
      {
        "id": "sop1",
        "name": "sopName"
      }
    ]
  }
]

芒果操场的代码可以在这里找到：https://mongoplayground.net/p/tg-4aAk-O1e，谢谢你帮助一位新手。

Answer 1

与单个联接条件匹配的相等性

db.so.aggregate([
  {
    $lookup: {
      from: "sop",
      localField: "sops.id",
      foreignField: "id",
      as: "sop"
    }
  }
])

或者Ray提到的等效管道：

db.so.aggregate([
  {
    "$lookup": {
      "from": "sop",
      "let": {
        "sops_id": "$sops.id"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              $in: [
                "$id",
                "$$sops_id"
              ]
            }
          }
        }
      ],
      "as": "sop"
    }
  }
])