MongoDB聚合查询Lookup关联子对象数据为空问题求助

问题说明 #

使用MongoDB aggregate查询时，通过lookup操作关联子对象数据，返回结果中stickerData和prefixData始终为空数组，同时需要补充关联categoryData和subcategoryData。

当前查询语句 #

SubCategorySchema.aggregate([
 {
    $match: {
      lang: lang
    }
  }, 
  {
    $project: {
      '_id': 1, 'categoryId': 1, 'name': 1, 'slug': 1, 
 'meta_title': 1,'meta_description': 1,'meta_keywords': 1
    }
  },
  {
    $lookup: {
      "from": "collect_A",
      let: { cid: "$_id" },
      pipeline: [
        { $sort: { updatedAt: -1 } },
        {
          "$unwind": "$states"
        },
        {
          $match: {
            $expr: { $eq: [ "$sub_category_id", "$$cid" ] }
          }
        },
        {
          $lookup: {
            from: 'stickers',
            localField: "states.sticker_id",
            foreignField: '_id',
            pipeline: [{ $project: { _id: 1, name: 1 } }],
            as: 'stickerData'
          },
        },
        {
          $lookup: {
            from: 'prefixes',
            localField: "states.prefix_id",
            foreignField: '_id',
            pipeline: [{ $project: { _id: 1, name: 1 } }],
            as: 'prefixData'
          },
        },
      ],
      as: "states_data"
    }
  },
]).exec()

执行后返回结果 #

"sub_category":[
  {
  "_id":"649dbb996a6bd86bff8d4ed1",
  "name":"Latest",
  "debate":[
      {
        "_id":"649f1285c5723ed8f1dae1b2",
        "category_id":"649d1bdfee1670a67dcaa60c",
        "sub_category_id":"649dbb996a6bd86bff8d4ed1",
        "debates":{
            "sticker_id":"648edb011cda8abb8ea5a0c5",
            "prefix_id":"648edb011cda8abb8ea5a0c5",
            "poll_id":"648edb011cda8abb8ea5a0c5",
        },
        "stickerData":[],
        "prefixData":[],
      },
    ],
  },
]

期望输出结果 #

"sub_category":[
{
  "_id":"649dbb996a6bd86bff8d4ed1",
  "name":"Latest",
  "debate":[
      {
        "_id":"649f1285c5723ed8f1dae1b2",
        "category_id":"649d1bdfee1670a67dcaa60c",
        "sub_category_id":"649dbb996a6bd86bff8d4ed1",
        "debates":{
            "sticker_id":"648edb011cda8abb8ea5a0c5",
            "prefix_id":"648edb011cda8abb8ea5a0c5",
            "poll_id":"648edb011cda8abb8ea5a0c5",
        },
        "stickerData": [
          {
            "_id": "6496a947642cd78defff4ff3",
            "name": "Test Sticker 1",
          }
        ],
        "prefixData": [
          {
            "_id": "6496a920642cd78defff4fe8",
            "name": "Test prefix 1"
          }
        ],
        "categoryData": [
          {
            "_id": "64a13343aba20f9ecaf65407",
            "name": "category 1",
          }
        ],
        "subcategoryData": [
          {
            "_id": "64a133bfaba20f9ecaf65442",
            "name": "sub category 1",
            "slug": "sub-category-1",
          }
        ],
      },
    ],
  },
]

解决方法 #

问题分析 #

1. 字段路径错误：原查询中内层lookup的localField写为states.sticker_id，但实际数据中sticker_id和prefix_id存储在debates对象下，路径应为debates.sticker_id和debates.prefix_id。
2. 类型不匹配：collect_A中的sub_category_id是字符串类型，而SubCategory的_id是ObjectId类型，直接用$eq匹配会失败，需要将ObjectId转为字符串后再比较。
3. 缺少额外关联：需要添加categoryData和subcategoryData的lookup操作，关联对应集合。

修正后的查询语句 #

SubCategorySchema.aggregate([
  {
    $match: { lang: lang }
  },
  {
    $project: {
      '_id': 1, 'categoryId': 1, 'name': 1, 'slug': 1,
      'meta_title': 1, 'meta_description': 1, 'meta_keywords': 1
    }
  },
  {
    $lookup: {
      "from": "collect_A",
      let: { cid: "$_id" },
      pipeline: [
        { $sort: { updatedAt: -1 } },
        {
          $match: {
            $expr: { $eq: [ "$sub_category_id", { $toString: "$$cid" } ] }
          }
        },
        {
          $lookup: {
            from: 'stickers',
            localField: "debates.sticker_id",
            foreignField: '_id',
            pipeline: [{ $project: { _id: 1, name: 1 } }],
            as: 'stickerData'
          }
        },
        {
          $lookup: {
            from: 'prefixes',
            localField: "debates.prefix_id",
            foreignField: '_id',
            pipeline: [{ $project: { _id: 1, name: 1 } }],
            as: 'prefixData'
          }
        },
        {
          $lookup: {
            from: 'categories',
            localField: "category_id",
            foreignField: '_id',
            pipeline: [{ $project: { _id: 1, name: 1 } }],
            as: 'categoryData'
          }
        },
        {
          $lookup: {
            from: 'sub_categories',
            localField: "sub_category_id",
            foreignField: '_id',
            pipeline: [{ $project: { _id: 1, name: 1, slug: 1 } }],
            as: 'subcategoryData'
          }
        }
      ],
      as: "debate"
    }
  }
]).exec()

关键修改点 #

• 将$match中的$eq条件改为$eq: [ "$sub_category_id", { $toString: "$$cid" } ]，解决ObjectId与字符串的类型不匹配问题。
• 修正内层lookup的localField为debates.sticker_id和debates.prefix_id，匹配实际数据结构。
• 添加categoryData和subcategoryData的lookup操作，关联对应集合并指定返回字段。
• 移除不必要的$unwind: "$states"，原数据中不存在states字段，无需展开。

内容的提问来源于stack exchange，提问作者Shreyas H