MongoDB $lookup无法关联question与topic表问题排查求助

我拥有question和topic两张表，尝试使用Mongoose的aggregate配合$lookup进行表关联，规则为question表的topic_id字段匹配topic表的_id字段，但返回结果中topic字段始终为空数组。相关代码、表结构及数据详情如下：

关联代码 #

let result = await Question.Question.aggregate([{
    $lookup: { from: 'topic', localField: 'topic_id', foreignField: '_id', as: 'topic' }
}]);

返回结果示例 #

0
_id "644651ddb96106c0065bf539"
question    "question 1"
topic_id    "64464779b31146ced5d0ebbe"
explanation "programs"
__v 0
topic   []
1   
_id "64466045c886fb9d5be3f442"
question    "Which is programing languge"
topic_id    "64466021c886fb9d5be3f440"
explanation "Choose programing languages"
__v 0
topic   []

Question模型代码 #

import { Schema, model } from "mongoose";
const questionSchema = new Schema({ 
            question: {
                type: String,
                required:true,
            },
            topic_id: {
                type: String,
                required:true,
            },
            explanation: {
                type: String,
                required:true,
            },
});

const Question = model("question", questionSchema);
export default { Question };

Topic模型代码 #

import { Schema, model } from "mongoose";

const topicSchema = new Schema({ 
            name: {
                type: String,
                required:true,
            },
});

const Topic = model("topic", topicSchema);
export default { Topic };

Topic表数据 #

_id
64464779b31146ced5d0ebbe
name
"Programming"
__v
0

_id
64466021c886fb9d5be3f440
name
"Programing languages"
__v
0

核心问题是类型不匹配：question表的topic_id定义为String类型，而topic表的_id是MongoDB默认的ObjectId类型，$lookup匹配时要求类型严格一致，因此无法匹配成功。

方案1：修改Question模型的topic_id类型为ObjectId #

将question表的topic_id字段类型改为ObjectId，并同步已有数据的类型：

// 修改后的Question模型
import { Schema, model, Types } from "mongoose";
const questionSchema = new Schema({ 
    question: {
        type: String,
        required:true,
    },
    topic_id: {
        type: Types.ObjectId, // 改为ObjectId类型
        required:true,
        ref: 'topic' // 可选，用于后续populate操作
    },
    explanation: {
        type: String,
        required:true,
    },
});

const Question = model("question", questionSchema);
export default { Question };

然后批量转换已有数据的topic_id为ObjectId：

await Question.Question.updateMany(
  {},
  [ { $set: { topic_id: { $toObjectId: "$topic_id" } } } ]
);

方案2：在aggregate中动态转换类型匹配 #

如果不想修改模型和数据结构，可以在$lookup阶段前后添加类型转换逻辑：

方式A：将question的topic_id转为ObjectId

let result = await Question.Question.aggregate([
  {
    $addFields: {
      // 临时将字符串类型的topic_id转为ObjectId
      topic_id_obj: { $toObjectId: "$topic_id" }
    }
  },
  {
    $lookup: { 
      from: 'topic', 
      localField: 'topic_id_obj', 
      foreignField: '_id', 
      as: 'topic' 
    }
  },
  // 可选：移除临时生成的字段
  { $project: { topic_id_obj: 0 } }
]);

方式B：将topic的_id转为字符串

let result = await Question.Question.aggregate([
  {
    $lookup: { 
      from: 'topic', 
      let: { q_topic_id: "$topic_id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: [ { $toString: "$_id" }, "$$q_topic_id" ] }
          }
        }
      ],
      as: 'topic'
    }
  }
]);

内容的提问来源于stack exchange，提问作者Papp Zoltán