Elasticsearch查询按全局term分组：返回含匹配记录的分组全部数据

实现方案 #

MongoDB 聚合管道实现 #

核心逻辑是先对分组做匹配标记，再过滤符合要求的分组，聚合代码如下：

db.collection.aggregate([
  // 按product_id分组，存储全量原始记录，同时标记分组是否存在color=red的记录
  {
    $group: {
      _id: "$product_id",
      values: { $push: "$$ROOT" },
      hasMatch: { $max: { $cond: [{ $eq: ["$color", "red"] }, true, false] } }
    }
  },
  // 仅保留存在匹配记录的分组
  {
    $match: {
      hasMatch: true
    }
  },
  // 调整输出格式匹配预期
  {
    $project: {
      _id: 0,
      product_id: "$_id",
      values: 1
    }
  }
])

逻辑说明：

• $cond 对每条记录做匹配判断，符合查询条件返回true，否则返回false
• $max 对分组内的布尔值做聚合，只要分组内有一条匹配记录，hasMatch 就为true
• $$ROOT 指代整条原始记录，保证values数组中存储的是完整的原始数据

SQL实现（支持子查询的关系型数据库） #

如果使用关系型数据库，可以通过子查询先筛选出符合条件的product_id，再关联全量数据分组聚合，以MySQL为例：

SELECT 
  t1.product_id,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'product_id', t1.product_id,
      'color', t1.color
      -- 其余字段按需补充
    )
  ) AS `values`
FROM your_table t1
INNER JOIN (
  SELECT DISTINCT product_id FROM your_table WHERE color = 'red'
) t2 ON t1.product_id = t2.product_id
GROUP BY t1.product_id

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