如何在MongoDB旧版本中不使用setIntersection实现聚合查询求交集

使用MongoDB ^3.1.4旧版本，无法使用$setIntersection操作符，需要获取促销（sale）分类与指定分类的交集产品。每个产品包含：

• category_id：主分类字段
• category_ids：附加分类数组（可能包含促销分类ID）

原查询无法正常工作，代码如下：

itemsAggregation.push({
            $match: {
                $or: [
                    {
                        category_ids: {
                            $in: [params.category_id, '6273c1aabc7df41d6e3518da']
                        }
                    },
                    { category_id: params.category_id }
                ]
            }
        });

其中params.category_id是指定分类ID，'6273c1aabc7df41d6e3518da'是促销分类ID。

示例数据 #

单个产品 #

{"_id":{"$oid":"61f12c5cd0b7910e766f0ba9"},"date_created":{"$date":"2022-01-26T11:11:24.809Z"},"date_updated":{"$date":"2022-05-04T14:30:06.128Z"},"images":[{"id":{"$oid":"62aae69fe3ba26163154f821"},"alt":"","position":99,"filename":"zarakaput.webp"},{"id":{"$oid":"62aae6a3e3ba26163154f822"},"alt":"","position":99,"filename":"kaput2.webp"}],"dimensions":{"length":0,"width":0,"height":0},"refund_approved_count":3,"refund_rejected_count":5,"name":"Kaput","description":"<p>- vrhunska kolica</p>\n<p>- bas bas dobra kolica</p>","meta_description":"muski kaput - meta","meta_title":"","tags":["test","AKCIJA","novo"],"attributes":[],"enabled":true,"discontinued":false,"slug":"kaput","sku":"123","code":"","tax_class":"","related_product_ids":[{"$oid":"61f11ef3c120b7097f8eb01c"},{"$oid":"61f143d36df8b5122d0fd0f1"},{"$oid":"61f11ef3c120b7097f8eb01c"}],"prices":[],"cost_price":0,"regular_price":100,"sale_price":90.5,"quantity_inc":1,"quantity_min":1,"weight":1,"stock_quantity":12,"position":null,"date_stock_expected":null,"date_sale_from":{"$date":"2022-05-26T12:31:40.223Z"},"date_sale_to":{"$date":"2022-05-27T22:00:00.000Z"},"stock_tracking":false,"stock_preorder":false,"stock_backorder":false,"category_id":{"$oid":"61f11ef3c120b7097f8eb014"},"category_ids":[{"$oid":"61f12bacd0b7910e766f0ba8"},{"$oid":"6239e69aae10f91ee7273ea0"}],"options":[{"id":{"$oid":"61f7fa379c70f8312a6571b5"},"name":"Boja","control":"select","required":true,"position":0,"values":[{"id":{"$oid":"6238424385670225ec539803"},"name":"Maximum Blue"},{"id":{"$oid":"628b771ee225162f4937ab7b"},"name":"Crvena"},{"id":{"$oid":"628b77d3e225162f4937ab84"},"name":"Zuta"}]},{"id":{"$oid":"62a1f322ca0a4fb7e00c886b"},"name":"Velicina","control":"select","required":true,"position":0,"values":[{"id":{"$oid":"62a1f33cca0a4fb7e00c886d"},"name":"22"},{"id":{"$oid":"62a1f33dca0a4fb7e00c886e"},"name":"23"}]},{"id":{"$oid":"62df943a48cf1fd956cb56bd"},"name":"New option","control":"select","required":true,"position":0,"values":[]}],"variants":[{"id":{"$oid":"62a9cea32adefd807f222262"},"sku":"","price":100,"stock_quantity":12,"weight":1,"options":[{"option_id":{"$oid":"61f7fa379c70f8312a6571b5"},"value_id":{"$oid":"628b771ee225162f4937ab7b"}},{"option_id":{"$oid":"62a1f322ca0a4fb7e00c886b"},"value_id":{"$oid":"62a1f33cca0a4fb7e00c886d"}}],"image":"62a9cea32adefd807f222262.webp"},{"id":{"$oid":"62a9cebe2adefd807f222263"},"sku":"","price":100,"stock_quantity":12,"weight":1,"options":[{"option_id":{"$oid":"61f7fa379c70f8312a6571b5"},"value_id":{"$oid":"6238424385670225ec539803"}},{"option_id":{"$oid":"62a1f322ca0a4fb7e00c886b"},"value_id":{"$oid":"62a1f33cca0a4fb7e00c886d"}}],"image":"62a9cebe2adefd807f222263.webp"}],"refund_count":8,"brand":"Testni brend"}

单个促销分类 #

{"_id":{"$oid":"62d68dd8b7d9b8ca0f77d6e4"},"date_created":{"$date":"2022-07-19T10:56:24.279Z"},"date_updated":{"$date":"2022-07-19T10:57:03.019Z"},"image":"","name":"Sale","description":"","meta_description":"","meta_title":"","enabled":true,"sort":"","parent_id":null,"position":"7","code":"","name_translation":"Akcija","slug":"sale","totalProducts":10}

原查询逻辑错误：它会把只要附加分类包含指定/促销分类，或主分类是指定分类的产品全部查出，不符合「同时属于促销分类和指定分类」的交集需求。

针对MongoDB 3.1.4（不支持$setIntersection），提供两种实现方式：

方法一：直接用$and组合条件 #

明确要求产品同时满足「属于促销分类」和「属于指定分类」两个条件：

const SALE_CATEGORY_ID = '6273c1aabc7df41d6e3518da';
const targetCategoryId = params.category_id;

itemsAggregation.push({
  $match: {
    $and: [
      // 属于促销分类：主分类是促销，或附加分类包含促销
      {
        $or: [
          { category_id: SALE_CATEGORY_ID },
          { category_ids: SALE_CATEGORY_ID }
        ]
      },
      // 属于指定分类：主分类是指定，或附加分类包含指定
      {
        $or: [
          { category_id: targetCategoryId },
          { category_ids: targetCategoryId }
        ]
      }
    ]
  }
});

方法二：聚合构造完整分类数组再判断 #

如果需要更灵活的分类判断，可先合并主分类与附加分类为一个数组，再筛选同时包含两个目标ID的产品：

const SALE_CATEGORY_ID = '6273c1aabc7df41d6e3518da';
const targetCategoryId = params.category_id;

itemsAggregation.push(
  // 合并主分类和附加分类为完整数组
  {
    $project: {
      ..."$$ROOT",
      all_categories: {
        $concatArrays: [
          [ "$category_id" ], // 将主分类转为数组
          "$category_ids"
        ]
      }
    }
  },
  // 筛选同时包含两个目标分类的产品
  {
    $match: {
      all_categories: SALE_CATEGORY_ID,
      all_categories: targetCategoryId
    }
  }
);

注意事项 #

• 方法一逻辑直接，性能更优，适合简单场景；方法二适合需要扩展更多分类判断的场景；
• 如果分类ID存储为ObjectId类型，查询时需用ObjectId(SALE_CATEGORY_ID)而非字符串，避免匹配失败。

内容的提问来源于Stack Exchange，提问作者Faris Sahman