如何在Elasticsearch中按status分组统计各平台浏览量总和

解决方案查询DSL #

{
  "size": 0,
  "aggs": {
    "status_groups": {
      "terms": {
        "field": "status.keyword"
      },
      "aggs": {
        "platform_views": {
          "nested": {
            "path": "video.platforms"
          },
          "aggs": {
            "platform_names": {
              "terms": {
                "field": "video.platforms.names.keyword"
              },
              "aggs": {
                "total_views": {
                  "sum": {
                    "field": "video.platforms.views"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

查询逻辑说明 #

1. 外层status_groups聚合：按status字段（需用.keyword后缀匹配精确字符串值）分组，将文档划分为draft和published两个独立桶。
2. 嵌套platform_views聚合：在每个status桶内，通过nested聚合进入video.platforms数组字段的层级，处理数组内的嵌套文档。
3. 平台分组与求和：在嵌套层级内，platform_names按平台名称分组，total_views聚合计算每个平台的浏览量总和。

ES原生返回结果 #

"aggregations": {
  "status_groups": {
    "doc_count_error_upper_bound": 0,
    "sum_other_doc_count": 0,
    "buckets": [
      {
        "key": "published",
        "doc_count": 2,
        "platform_views": {
          "doc_count": 4,
          "platform_names": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "youtube",
                "doc_count": 2,
                "total_views": {
                  "value": 7.0
                }
              },
              {
                "key": "dailymotion",
                "doc_count": 2,
                "total_views": {
                  "value": 13.0
                }
              }
            ]
          }
        }
      },
      {
        "key": "draft",
        "doc_count": 1,
        "platform_views": {
          "doc_count": 2,
          "platform_names": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "youtube",
                "doc_count": 1,
                "total_views": {
                  "value": 1.0
                }
              },
              {
                "key": "dailymotion",
                "doc_count": 1,
                "total_views": {
                  "value": 4.0
                }
              }
            ]
          }
        }
      }
    ]
  }
}

转换为期望格式（客户端处理示例） #

ES原生返回为嵌套桶结构，需在客户端做简单转换以得到目标格式，以下是Python示例：

# 假设agg_result为ES返回的aggregations字段数据
target_result = []
for status_bucket in agg_result['status_groups']['buckets']:
    views_list = []
    for platform_bucket in status_bucket['platform_views']['platform_names']['buckets']:
        views_list.append({
            "names": platform_bucket['key'],
            "views": platform_bucket['total_views']['value']
        })
    target_result.append({
        "status": status_bucket['key'],
        "views": views_list
    })

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