如何在Elasticsearch嵌套字段中实现计划关联频次统计聚合查询？

我有一批包含嵌套字段planList的文档，该字段是固定长度为2或3的对象数组，每个对象包含planName、planType、planId三个关键字段。需要编写聚合查询语句，返回所有planName的列表，以及每个planName最常伴随出现的计划及其频次。

示例数据 #

{
  "planList": [
    {"planName": "a", "planType": "x", "planId": 1},
    {"planName": "b", "planType": "x", "planId": 2}
  ]
}
{
  "planList": [
    {"planName": "a", "planType": "x", "planId": 1},
    {"planName": "b", "planType": "x", "planId": 2},
    {"planName": "c", "planType": "y", "planId": 3}
  ]
}
{
  "planList": [
    {"planName": "a", "planType": "x", "planId": 1},
    {"planName": "c", "planType": "y", "planId": 3}
  ]
}
{
  "planList": [
    {"planName": "d", "planType": "y", "planId": 4},
    {"planName": "c", "planType": "y", "planId": 3}
  ]
}

期望分析结果 #

plan A was found with plan B and plan C 2 times (draw between plan B and C)
plan B was found with plan A 2 times
plan C was found with plan A 2 times
plan D was found with plan C one time

基础查询语句 #

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "event": "comparePlans"
          }
        }
      ]
    }
  }
}

索引映射 #

{
  "planList": {
    "type": "nested",
    "properties": {
      "planId": {
        "type": "keyword"
      },
      "planName": {
        "type": "keyword"
      },
      "planType": {
        "type": "keyword"
      }
    }
  },
  "event": {
    "type": "keyword",
    "null_value": "none"
  }
}

聚合查询实现 #

以下是实现需求的完整聚合查询语句：

{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "event": "comparePlans"
          }
        }
      ]
    }
  },
  "size": 0,
  "aggs": {
    "nested_plans": {
      "nested": {
        "path": "planList"
      },
      "aggs": {
        "group_by_plan": {
          "terms": {
            "field": "planList.planName",
            "size": 1000
          },
          "aggs": {
            "reverse_nested": {
              "reverse_nested": {},
              "aggs": {
                "related_plans": {
                  "nested": {
                    "path": "planList"
                  },
                  "aggs": {
                    "exclude_self": {
                      "filter": {
                        "bool": {
                          "must_not": [
                            {
                              "term": {
                                "planList.planName": "{{group_by_plan.key}}"
                              }
                            }
                          ]
                        }
                      },
                      "aggs": {
                        "related_plan_names": {
                          "terms": {
                            "field": "planList.planName",
                            "size": 1000
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

聚合逻辑说明 #

1. nested_plans：进入planList嵌套字段上下文，开启嵌套聚合操作。
2. group_by_plan：按planName分组，得到每个计划的独立分组。
3. reverse_nested：回到根文档上下文，获取当前计划所在文档的完整数据。
4. related_plans：再次进入嵌套字段上下文，处理当前分组计划的关联计划。
5. exclude_self：过滤掉当前分组的计划本身，仅保留伴随出现的其他计划。
6. related_plan_names：统计每个关联计划的出现频次。

结果后处理说明 #

Elasticsearch聚合仅返回原始统计数据，你需要在客户端做二次处理：

• 对每个计划的关联频次排序，提取最高频次的计划
• 处理并列最高的情况（如示例中plan A的关联计划B、C频次相同）
• 按期望格式输出文本结果

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