MongoDB使用$lookup关联文档失败，返回空数组问题求助

我尝试用MongoDB的aggregate函数关联student和wall两个集合，但调用后返回空数组[]。

原查询语句 #

db.student.aggregate([
  {
    "$match": {
      _id: "63c4c245188267e988d690e2"
    }
  },
  {
    "$lookup": {
      "from": "wall",
      "localField": "_user",
      "foreignField": "_id",
      "as": "wallpost"
    }
  }
])

执行结果 #

[
  {
    "_id": "63c4c245188267e988d690e2",
    "hereFor": [
      {
        "mainTag": "study",
        "subtag": [
          "studybuddy",
          "findtutor"
        ]
      }
    ],
    "lastName": "Kumar",
    "name": "Kamal",
    "profilePicture": [
      "https://airustudentimages.s3.ca-central-1.amazonaws.com/1673588594404-ba7777ef676f439f86aa612e8be67fd9"
    ],
    "wallpost": []
  }
]

集合数据 #

Student集合 #

[
  {
    "_id": "63c4c245188267e988d690e2",
    "name": "Kamal",
    "lastName": "Kumar",
    "profilePicture": [
      "https://airustudentimages.s3.ca-central-1.amazonaws.com/1673588594404-ba7777ef676f439f86aa612e8be67fd9"
    ],
    "hereFor": [
      {
        "mainTag": "study",
        "subtag": [
          "studybuddy",
          "findtutor"
        ]
      }
    ]
  },
  {
    "_id": "63c3965c201a1d738ab6867e",
    "name": "Suraksha",
    "lastName": "Singh",
    "profilePicture": [
      "https://airustudentimages.s3.ca-central-1.amazonaws.com/1673762449670-a8bdf9b9b0faf3ad84e0a5bc76e32fb8"
    ],
    "hereFor": [
      {
        "mainTag": "study",
        "subtag": [
          "studybuddy",
          "findtutor"
        ]
      }
    ]
  }
]

Wall集合 #

[
  {
    "_id": "63c4c92a188267e988d690e3",
    "_user": "63c3965c201a1d738ab6867e",
    "isSponsered": false,
    "description": "Hello test case ",
    "tag": {
      "mainTag": "hostels"
    },
    "createdAt": "1673766717308",
    "updatedAt": "1673766717308"
  },
  {
    "_id": "63c4cc2b188267e988d690e5",
    "_user": "63c3965c201a1d738ab6867e",
    "isSponsered": false,
    "description": "Hello test case 22 ",
    "tag": {
      "mainTag": "hostels"
    },
    "createdAt": "1673766717308",
    "updatedAt": "1673766717308"
  }
]

问题原因及解决办法 #

1. 字段映射错误：原查询中$lookup的localField和foreignField完全搞反了。Student集合中不存在_user字段，正确的关联逻辑应该是用Student的_id匹配Wall集合的_user字段。
2. 测试数据匹配问题：当前$match筛选的Student（_id: "63c4c245188267e988d690e2"）在Wall集合中没有对应的记录，所有Wall数据都属于另一个Student（_id: "63c3965c201a1d738ab6867e"）。

修正后的查询语句 #

db.student.aggregate([
  {
    "$match": {
      _id: "63c3965c201a1d738ab6867e" // 切换到有Wall记录的Student
    }
  },
  {
    "$lookup": {
      "from": "wall",
      "localField": "_id", // Student的主键
      "foreignField": "_user", // Wall中关联Student的外键
      "as": "wallpost"
    }
  }
])

修正后的执行结果 #

[
  {
    "_id": "63c3965c201a1d738ab6867e",
    "name": "Suraksha",
    "lastName": "Singh",
    "profilePicture": [
      "https://airustudentimages.s3.ca-central-1.amazonaws.com/1673762449670-a8bdf9b9b0faf3ad84e0a5bc76e32fb8"
    ],
    "hereFor": [
      {
        "mainTag": "study",
        "subtag": [
          "studybuddy",
          "findtutor"
        ]
      }
    ],
    "wallpost": [
      {
        "_id": "63c4c92a188267e988d690e3",
        "_user": "63c3965c201a1d738ab6867e",
        "isSponsered": false,
        "description": "Hello test case ",
        "tag": { "mainTag": "hostels" },
        "createdAt": "1673766717308",
        "updatedAt": "1673766717308"
      },
      {
        "_id": "63c4cc2b188267e988d690e5",
        "_user": "63c3965c201a1d738ab6867e",
        "isSponsered": false,
        "description": "Hello test case 22 ",
        "tag": { "mainTag": "hostels" },
        "createdAt": "1673766717308",
        "updatedAt": "1673766717308"
      }
    ]
  }
]

内容的提问来源于stack exchange，提问作者Abdur Rehman Khalid