如何在MongoDB聚合管道中对嵌套数组执行文本搜索匹配

集合数据 #

[
  {
    "_id": {
      "$oid": "63f4551a871a50c6a39dcaa3"
    },
    "supplier_name": "Willy's Wines",
    "supplier_id": "WLW-1",
    "supplier_email": [
      {
        "supplier_email_name": "Admin",
        "supplier_email_address": "admin@blah.com",
        "_id": {
          "$oid": "63f4551a871a50c6a39dcaa4"
        }
      }
    ],
    "supplier_payment_terms": "30 Days",
    "supplier_delivery_terms": "Next Day",
    "supplier_items_catalogue": [
      {
        "supplier_item_sku_id": "WN001",
        "supplier_item_sku_description": "Blue Mountains Sauvignon Blanc",
        "supplier_item_uom": "BO750",
        "supplier_item_uom_description": "750ml bottle",
        "supplier_item_price": 42,
        "supplier_item_quantity_per_uom": 1,
        "_id": {
          "$oid": "63f4551a871a50c6a39dcaa8"
        }
      },
      {
        "supplier_item_sku_id": "WN002",
        "supplier_item_sku_description": "Blue Mountains Pinot Noir",
        "supplier_item_uom": "BO750",
        "supplier_item_uom_description": "750ml bottle",
        "supplier_item_price": 37,
        "supplier_item_quantity_per_uom": 1,
        "_id": {
          "$oid": "63f4551a871a50c6a39dcaa9"
        }
      }
    ]
  },
  {
    "_id": {
      "$oid": "63f4551a871a50c6a39dcaa3"
    },
    "supplier_name": "Hills Liqour Store",
    "supplier_id": "HLL-1",
    "supplier_email": [
      {
        "supplier_email_name": "Admin",
        "supplier_email_address": "admin@blah.com",
        "_id": {
          "$oid": "63f4551a871a50c6a39dcaa5"
        }
      }
    ],
    "supplier_payment_terms": "30 Days",
    "supplier_delivery_terms": "Next Day",
    "supplier_items_catalogue": [
      {
        "supplier_item_sku_id": "HL001",
        "supplier_item_sku_description": "Table Mountain Sauvignon Blanc",
        "supplier_item_uom": "BO750",
        "supplier_item_uom_description": "750ml bottle",
        "supplier_item_price": 43,
        "supplier_item_quantity_per_uom": 1,
        "_id": {
          "$oid": "63f4551a871a50c6a39dcab0"
        }
      },
      {
        "supplier_item_sku_id": "HL002",
        "supplier_item_sku_description": "Table Mountain Pinot Noir",
        "supplier_item_uom": "BO750",
        "supplier_item_uom_description": "750ml bottle",
        "supplier_item_price": 36,
        "supplier_item_quantity_per_uom": 1,
        "_id": {
          "$oid": "63f4551a871a50c6a39dcab1"
        }
      },
      {
        "supplier_item_sku_id": "HL003",
        "supplier_item_sku_description": "Uluru Pinot Grigio",
        "supplier_item_uom": "BO750",
        "supplier_item_uom_description": "750ml bottle",
        "supplier_item_price": 34,
        "supplier_item_quantity_per_uom": 1,
        "_id": {
          "$oid": "63f4551a871a50c6a39dcab2"
        }
      }
    ]
  }
]

期望结果 #

搜索文本为Pinot时，返回以下格式数据：

[
  {
    "supplier_name": "Willy's Wines",
    "supplier_id": "WLW-1",
    "supplier_payment_terms": "30 Days",
    "supplier_item_sku_id": "WN002",
    "supplier_item_sku_description": "Blue Mountains Pinot Noir",
    "supplier_item_uom_description": "750ml bottle",
    "supplier_item_price": 37
  },
  {
    "supplier_name": "Hills Liqour Store",
    "supplier_id": "HLL-1",
    "supplier_payment_terms": "30 Days",
    "supplier_item_sku_id": "HL002",
    "supplier_item_sku_description": "Table Mountain Pinot Noir",
    "supplier_item_uom_description": "750ml bottle",
    "supplier_item_price": 36
  },
  {
    "supplier_name": "Hills Liqour Store",
    "supplier_id": "HLL-1",
    "supplier_payment_terms": "30 Days",
    "supplier_item_sku_id": "HL003",
    "supplier_item_sku_description": "Uluru Pinot Grigio",
    "supplier_item_uom_description": "750ml bottle",
    "supplier_item_price": 34
  }
]

现有实现（仅支持精确匹配） #

当前查询只能匹配完整描述文本，无法实现模糊文本搜索：

db.collection.aggregate([
  {
    "$match": {
      "supplier_items_catalogue.supplier_item_sku_description": "Uluru Pinot Grigio"
    }
  },
  {
    $unwind: "$supplier_items_catalogue"
  },
  {
    $match: {
      "supplier_items_catalogue.supplier_item_sku_description": "Uluru Pinot Grigio"
    }
  },
  {
    $project: {
      supplier_name: "$supplier_name",
      supplier_id: "$supplier_id",
      payment_terms: "$supplier_payment_terms",
      sku_id: "$supplier_items_catalogue.supplier_item_sku_id",
      sku_description: "$supplier_items_catalogue.supplier_item_sku_description",
      uom_description: "$supplier_item_uom_description",
      item_price: "$supplier_item_price"
    }
  }
])

文本搜索实现方案 #

已创建文本索引的前提下，修改聚合管道如下：

1. 确保文本索引正确创建 #

如果尚未创建，执行以下命令：

db.collection.createIndex(
  { "supplier_items_catalogue.supplier_item_sku_description": "text" }
)

2. 修改后的聚合管道 #

db.collection.aggregate([
  // 第一步：用文本搜索过滤出包含匹配商品的供应商文档，利用索引提升性能
  {
    $match: {
      $text: { $search: "Pinot" }
    }
  },
  // 展开商品目录数组，将每个商品转换为独立文档
  { $unwind: "$supplier_items_catalogue" },
  // 第二步：过滤出当前商品描述匹配搜索词的项，排除不匹配的商品
  {
    $match: {
      $text: { $search: "Pinot" }
    }
  },
  // 投影出目标字段，修正原查询中的路径错误，隐藏默认_id
  {
    $project: {
      _id: 0,
      supplier_name: 1,
      supplier_id: 1,
      supplier_payment_terms: 1,
      supplier_item_sku_id: "$supplier_items_catalogue.supplier_item_sku_id",
      supplier_item_sku_description: "$supplier_items_catalogue.supplier_item_sku_description",
      supplier_item_uom_description: "$supplier_items_catalogue.supplier_item_uom_description",
      supplier_item_price: "$supplier_items_catalogue.supplier_item_price"
    }
  }
])

关键说明 #

• 两次$match：第一次快速过滤出存在匹配商品的供应商文档，减少后续处理的数据量；第二次在展开数组后，只保留真正匹配的商品项
• 文本搜索支持分词、停用词处理，比正则匹配更适合自然语言搜索场景
• 修正了原投影中的字段路径错误，确保所有商品相关字段从supplier_items_catalogue嵌套对象中正确提取
• 添加_id: 0隐藏默认返回的_id字段，与期望结果格式一致

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