具有不同本地欄位的mongodb$lookup-有解無憂

我有三個不同的系列。下面的例子。

Collection 1
{
    _id: ObjectId('123'),
    studentnumber: 123,
    current_position: 'A',
    schoolId: ObjectId('387')
}

Collection 2
{
    _id: ObjectId('456'),
    studentId: ObjectId('123'),
    studentnumber: 123,
    firstname: 'John',
    firstname: 'Doe',
    schoolId: ObjectId('543')
}

Collection 3
{
    _id: ObjectId('387'),
    schoolName: 'Some school'
},
{
    _id: ObjectId('543'),
    schoolName: 'Some other school'
},

我已經有一個看起來像這樣的聚合查詢。我對 MongoDB 聚合完全陌生。我想知道是否有任何方法可以使用 $lookup 的 localField 中不同集合中的欄位。

db.collection1.aggregate([
    ///
    $lookup: {
      from: "collection2",
      localField: "studentnumber",
      foreignField: "studentnumber",
      as: "studentnumber",
    },
    ///
    $lookup: {
      from: "collection3",
      localField: "schoolId",
      foreignField: "_id",
      as: "schoolId",
    }
///
])

如何在第二個中使用schoolIdfrom collection2$lookuplocalField

電流輸出：

{
    _id: ObjectId('123'),
    firstname: 'John',
    firstname: 'Doe',
    current_position: 'A',
    school: {
        _id: ObjectId('387'),
        schoolName: 'Some school'
    }
}

預期輸出：

{
    _id: ObjectId('123'),
    firstname: 'John',
    firstname: 'Doe',
    current_position: 'A',
    school: {
        _id: ObjectId('543'),
        schoolName: 'Some other school'
    }
}

編輯：更新了當前和預期的輸出。添加了輸入檔案。

uj5u.com熱心網友回復：

你可以試試pipelined. $lookup像這樣：

db.c1.aggregate([
  {
    "$lookup": {
      "from": "c2",
      "let": {
        id: "$studentnumber"
      },
      "pipeline": [
        {
          "$lookup": {
            "from": "c3",
            "localField": "schoolId",
            "foreignField": "_id",
            "as": "school"
          }
        },
        {
          "$match": {
            "$expr": {
              "$eq": [
                "$$id",
                "$studentId"
              ]
            }
          }
        }
      ],
      "as": "doc"
    }
  },
  {
    "$addFields": {
      "doc": {
        "$arrayElemAt": [
          "$doc",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "doc.school": {
        "$arrayElemAt": [
          "$doc.school",
          0
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 1,
      "firstname": 1,
      "lastname": 1,
      "current_position": 1,
      "school": "$doc.school"
    }
  }
])

在mongodb playground上檢查輸出。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/506441.html

標籤：mongodb 猫鼬聚合框架总计的

上一篇：React...constcastError=newCastError();...編輯操作將我退出（出現錯誤時我設定的條件）

下一篇：如何結合查找/圖形查找進行不區分大小寫檢查？