MongoDB聚合$lookup按category匹配vouchers返回空结果求解

现有集合结构 #

当前共3个集合：location、company、vouchers，其中company和vouchers的文档结构示例如下：

"company": [
    {
      "_id": "625ae79a51828244cef979d4",
      "name": "C1",
      "location": "L1",
      "category": "A"
    },
    {
      "_id": "625ba41651828244cefa138b",
      "name": "C2",
      "location": "L2",
      "category": "B"
    },
    {
      "_id": "625ba4d651828244cefa1951",
      "name": "C3",
      "location": "L3",
      "category": "B"
    }
]

"vouchers":[
   {
      "_id":"625ae79a51828244cef979d4",
      "name":"V1",
      "color":"#ad7f7f",
      "category":[
        "A",
        "B"
      ]
   },
   {
     "_id":"625ba41651828244cefa138b",
     "name":"V2",
     "color":"#9A348E",
     "category":[
       "A"
     ]
   },
   {
     "_id":"625ba4d651828244cefa1951",
     "name":"V3",
     "color":"#31263E",
     "category":[
       "B"
     ]
   },
   {
     "_id":"625ba4d651828244cefa1951",
     "name":"V4",
     "color":"#31263E",
     "category":[
        "C"
     ]
   }
]

预期输出结果 #

需要查询得到每个公司下匹配其分类的优惠券列表，预期结构如下：

"companies":[
  {
     "_id":"625ae79a51828244cef979d4",
     "name":"C1",
     "location":"L1",
     "category":"A",
     "vouchers":[
        {
           "_id":"625ae79a51828244cef979d4",
           "name":"V1",
           "color":"#ad7f7f",
           "category":[
              "A",
              "B"
           ]
        },
        {
           "_id":"625ba41651828244cefa138b",
           "name":"V2",
           "color":"#9A348E",
           "category":[
              "A"
           ]
        }
     ]
  },
  {
     "_id":"625ba41651828244cefa138b",
     "name":"C2",
     "location":"L2",
     "category":"B",
     "vouchers":[
        {
           "_id":"625ae79a51828244cef979d4",
           "name":"V1",
           "color":"#ad7f7f",
           "category":[
              "A",
              "B"
           ]
        },
        {
           "_id":"625ba4d651828244cefa1951",
           "name":"V3",
           "color":"#31263E",
           "category":[
              "B"
           ]
        }
     ]
  },
  {
     "_id":"625ba4d651828244cefa1951",
     "name":"C3",
     "location":"L3",
     "category":"C",
     "vouchers":[
        {
           "_id":"625ba4d651828244cefa1951",
           "name":"V4",
           "color":"#31263E",
           "category":[
              "C"
           ]
        }
     ]
  }
]

当前实现问题 #

当前采用MongoDB聚合管道实现：

1. 先用$geoWithin筛选符合范围的location文档
2. 通过$lookup关联查询对应的company文档
3. 编写$lookup阶段关联vouchers，尝试匹配分类一致的优惠券，代码如下：

$lookup: {
        from: "vouchers",
        let: { category: "$company.category" },
        pipeline: [
          {
            $match: {
              $expr: {
                $and: [{ $in: ["$category", "$$category"] }],
              },
            },
          },
        ],
        as: "vouchers",
      }

执行后返回的vouchers字段始终为空，无法得到预期结果。

核心错误点 #

$in操作符的参数顺序写反，导致匹配逻辑完全倒置：

• $in的语法规则为{ $in: [ <待查找值>, <数组类型的查找范围> ] }，作用是判断待查找值是否存在于后面的数组中
• 当前写法是$in: ["$category", "$$category"]：$category是vouchers文档中的分类字段（数组类型），$$category是从company传入的单个分类字符串，相当于判断「数组是否存在于字符串中」，永远无法匹配成功
• 正确逻辑应该是：判断company传入的单个分类值，是否存在于vouchers的分类数组中。

额外注意事项 #

$lookup关联company集合后，返回的company字段默认是数组类型，直接取$company.category会得到分类值组成的数组，而非单个分类值，需要先通过$unwind拆分数组为单条文档，再做vouchers关联。

修正后代码 #

拆分company文档后，关联vouchers的$lookup阶段写法如下：

$lookup: {
  from: "vouchers",
  let: { companyCategory: "$company.category" }, // 传入当前公司的分类值
  pipeline: [
    {
      $match: {
        $expr: {
          // 调换$in参数顺序：判断公司分类是否在优惠券的分类数组中
          $in: [ "$$companyCategory", "$category" ]
        }
      }
    }
  ],
  as: "vouchers"
}

按该写法调整后即可正确匹配到对应分类的优惠券列表。

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