如何用MongoDB聚合实现多集合多OR条件查询

完全可以实现，以下是对应你需求的MongoDB聚合管道代码，完美匹配原SQL的逻辑：

const query = "你的搜索关键词"; // 比如"Delba"、"pending"、"20348"

db.invoices.aggregate([
  // 关联customers集合，对应SQL的JOIN
  {
    $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "id",
      as: "customer"
    }
  },
  // 展开关联后的customer数组（每个invoice仅对应一个customer）
  { $unwind: "$customer" },
  // 多条件模糊匹配，对应SQL的WHERE子句
  {
    $match: {
      $or: [
        { "customer.id": { $regex: query, $options: "i" } }, // 匹配customers.id（不区分大小写）
        { "customer.name": { $regex: query, $options: "i" } }, // 匹配customers.name（不区分大小写）
        { amount: { $regex: new RegExp(query, "i") } }, // 自动将数字转字符串匹配，对应amount::text ILIKE
        { status: { $regex: query, $options: "i" } } // 匹配invoices.status（不区分大小写）
      ]
    }
  },
  // 筛选返回字段，对应SQL的SELECT
  {
    $project: {
      _id: 0,
      customer_id: 1,
      amount: 1,
      status: 1,
      "customer.name": 1
    }
  },
  // 按金额降序排序，对应SQL的ORDER BY
  { $sort: { amount: -1 } }
])

关键逻辑拆解： #

• $lookup：实现双集合关联，和SQL的JOIN逻辑一致，通过customer_id与customers.id建立匹配关系。
• $unwind：将$lookup返回的数组型customer字段展开为单个对象，方便后续的条件判断和字段提取。
• $match：用$or组合四个模糊匹配规则，$options: "i"实现不区分大小写的匹配，完全对应SQL的ILIKE；数字类型的amount会被MongoDB自动转为字符串参与正则匹配，和原SQL的amount::text效果一致。
• $project：仅保留业务需要的字段，过滤掉默认的_id等冗余字段。
• $sort：按amount字段降序排列，和原SQL的排序逻辑完全对齐。

测试示例： #

若搜索关键词为"Lee"，会返回customer_id为"20"的发票数据；若搜索"20348"，同样会匹配到金额为20348的这条记录。

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