如何在Elasticsearch中按指定payload.category获取唯一payload.value列表

基于给定的Elasticsearch文档映射，需根据指定的payload.category筛选，获取所有文档中对应payload.value的唯一值列表。

示例文档：

{ "docId":1, "payload": [
{
"category": "YEAR",
"value": "2012"
}, {
"category": "YEAR",
"value": "2014"
},
{
"category": "match",
"value": "aus vs india"
} ] }

{ "docId":2, "payload": [
{
"category": "YEAR",
"value": "2012"
}, {
"category": "YEAR",
"value": "2019"
} ] }

当指定payload.category为YEAR时，期望返回唯一值列表：2012、2014、2019。

{  
  "stage_video_clip_646f4c0fb780630373dfffdb" : {    
    "mappings" : {      
      "date_detection" : false,      
      "properties" : {        
        "@class" : {          
          "type" : "keyword"        
        },        
        "additionalProperties" : {          
          "properties" : {            
            "@class" : {              
              "type" : "keyword"            
            },            
            "eventId" : {              
              "type" : "text",              
              "fields" : {                
                "keyword" : {                  
                  "type" : "keyword",                  
                  "ignore_above" : 256                
              }            
            }          
          },          
          "linius:offset" : {            
            "type" : "text",            
            "fields" : {              
              "keyword" : {                
                "type" : "keyword",                
                "ignore_above" : 256              
              }            
            }          
          },          
          "opta:eventId" : {            
            "type" : "text",            
            "fields" : {              
              "keyword" : {                
                "type" : "keyword",                
                "ignore_above" : 256              
              }            
            }          
          },          
          "videoOffset" : {            
            "type" : "text",            
            "fields" : {              
              "keyword" : {                
                "type" : "keyword",                
                "ignore_above" : 256              
              }            
            }          
          }        
        },        
        "assetCreated" : {          
          "type" : "long"        
        },        
        "assetEndDate" : {          
          "type" : "long"        
        },        
        "assetId" : {          
          "type" : "keyword"        
        },        
        "assetStartDate" : {          
          "type" : "long"        
        },        
        "created" : {          
          "type" : "long"        
        },        
        "endTime" : {          
          "type" : "long"        
        },        
        "id" : {          
          "type" : "text",          
          "fields" : {            
            "keyword" : {              
              "type" : "keyword",              
              "ignore_above" : 256            
            }          
          }        
        },        
        "payload" : {          
          "type" : "nested",          
          "properties" : {            
            "@class" : {              
              "type" : "keyword"            
            },            
            "additionalProperties" : {              
              "properties" : {                
                "@class" : {                  
                  "type" : "keyword"                
                }              
              }            
            },            
            "category" : {              
              "type" : "keyword"            
            },            
            "clipId" : {              
              "type" : "keyword"            
            },            
            "confidence" : {              
              "type" : "double"            
            },            
            "endTime" : {              
              "type" : "long"            
            },            
            "indexerType" : {              
              "type" : "keyword"            
            },            
            "integerValue" : {              
              "type" : "long"            
            },            
            "payloadId" : {              
              "type" : "keyword"            
            },            
            "payloadValueSuggest" : {              
              "type" : "completion",              
              "analyzer" : "standard",              
              "preserve_separators" : true,              
              "preserve_position_increments" : true,              
              "max_input_length" : 10000            
            },            
            "startTime" : {              
              "type" : "long"            
            },            
            "suggestCategory" : {              
              "type" : "completion",              
              "analyzer" : "standard",              
              "preserve_separators" : true,              
              "preserve_position_increments" : true,              
              "max_input_length" : 10000            
            },            
            "suggestValueCategory" : {              
              "type" : "completion",              
              "analyzer" : "standard",              
              "preserve_separators" : true,              
              "preserve_position_increments" : true,              
              "max_input_length" : 10000,              
              "contexts" : [                
                {                  
                  "name" : "category-context",                  
                  "type" : "CATEGORY",                  
                  "path" : "payload.category"                
              ]            
            },            
            "value" : {              
              "type" : "text",              
              "fields" : {                
                "keyword" : {                  
                  "type" : "keyword",                  
                  "ignore_above" : 256                
              }            
            }          
          }        
        },        
        "startTime" : {          
          "type" : "long"        
        },        
        "tenantId" : {          
          "type" : "keyword"        
        },        
        "updated" : {          
          "type" : "long"        
      }    
    }  
  }
}

由于payload是nested类型，必须使用嵌套聚合正确处理内部字段，以下是实现查询的DSL：

{
  "size": 0,
  "aggs": {
    "filtered_payload": {
      "nested": {
        "path": "payload"
      },
      "aggs": {
        "match_category": {
          "filter": {
            "term": {
              "payload.category": "YEAR"
            }
          },
          "aggs": {
            "unique_values": {
              "terms": {
                "field": "payload.value.keyword",
                "size": 10000
              }
            }
          }
        }
      }
    }
  }
}

关键说明： #

1. size: 0：无需返回原始文档，仅获取聚合结果
2. nested聚合：指定路径为payload，确保Elasticsearch正确解析嵌套对象
3. filter聚合：筛选出payload.category等于目标值的嵌套文档
4. terms聚合：基于payload.value.keyword字段提取唯一值，size可根据实际需要调整，确保覆盖所有可能的唯一值

返回结果示例： #

{
  "aggregations": {
    "filtered_payload": {
      "doc_count": 4,
      "match_category": {
        "doc_count": 4,
        "unique_values": {
          "buckets": [
            {
              "key": "2012",
              "doc_count": 2
            },
            {
              "key": "2014",
              "doc_count": 1
            },
            {
              "key": "2019",
              "doc_count": 1
            }
          ]
        }
      }
    }
  }
}

从buckets的key字段即可提取目标唯一值列表：2012、2014、2019。

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