elasticsearch通过group by保存搜索

yrdbyhpb  于 2021-06-13  发布在  ElasticSearch
关注(0)|答案(2)|浏览(280)
index_name: my_data-2020-12-01
ticket_number: T123 
ticket_status: OPEN 
ticket_updated_time: 2020-12-01 12:22:12   

index_name: my_data-2020-12-01 
ticket_number: T124 
ticket_status: OPEN 
ticket_updated_time: 2020-12-01 12:32:11   

index_name: my_data-2020-12-02 
ticket_number: T123 
ticket_status: INPROGRESS 
ticket_updated_time: 2020-12-02 12:33:12   

index_name: my_data-2020-12-02 
ticket_number: T125 
ticket_status: OPEN 
ticket_updated_time: 2020-12-02 14:11:45

我想用groupbyticket\u number字段创建一个保存的搜索,获取具有最新票证状态(ticket\u status)的唯一文档。有可能吗?

vxbzzdmp

vxbzzdmp1#

如果你需要分组 ticket_number 字段,则也可以使用聚合
索引Map:

{
  "mappings": {
    "properties": {
      "ticket_updated_time": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      }
    }
  }
}

搜索查询:

{
  "size": 0,
  "aggs": {
    "unique_id": {
      "terms": {
        "field": "ticket_number.keyword",
        "order": {
          "latestOrder": "desc"
        }
      },
      "aggs": {
        "latestOrder": {
          "max": {
            "field": "ticket_updated_time"
          }
        }
      }
    }
  }
}

搜索结果:

"buckets": [
        {
          "key": "T125",
          "doc_count": 1,
          "latestOrder": {
            "value": 1.606918305E12,
            "value_as_string": "2020-12-02 14:11:45"
          }
        },
        {
          "key": "T123",
          "doc_count": 2,
          "latestOrder": {
            "value": 1.606912392E12,
            "value_as_string": "2020-12-02 12:33:12"
          }
        },
        {
          "key": "T124",
          "doc_count": 1,
          "latestOrder": {
            "value": 1.606825931E12,
            "value_as_string": "2020-12-01 12:32:11"
          }
        }
      ]
nkoocmlb

nkoocmlb2#

您可以简单地再次查询,我假设您使用kibana进行可视化。在查询中,需要根据 ticket_number 排序依据 ticket_updated_time .
工作示例
索引Map

{
    "mappings": {
        "properties": {
            "ticket_updated_time": {
                "type": "date"
            },
            "ticket_number" :{
                "type" : "text"
            },
            "ticket_status" : {
                "type" : "text"
            }
        }
    }
}

索引示例文档

{
    "ticket_number": "T123",
    "ticket_status": "OPEN",
    "ticket_updated_time": "2020-12-01T12:22:12"
}

{
    "ticket_number": "T123",
    "ticket_status": "INPROGRESS",
    "ticket_updated_time": "2020-12-02T12:33:12"
}

现在您可以看到,两个示例文档都属于同一个票证号,状态和更新时间不同。
搜索查询

{
    "size" : 1, // fetch only the latest status document, if you remove this, will get other ticket with different status.
    "query": {
        "bool": {
            "filter": [
                {
                    "match": {
                        "ticket_number": "T123"
                    }
                }
            ]
        }
    },
    "sort": [
        {
            "ticket_updated_time": {
                "order": "desc"
            }
        }
    ]
}

和搜索结果

"hits": [
            {
                "_index": "65180491",
                "_type": "_doc",
                "_id": "2",
                "_score": null,
                "_source": {
                    "ticket_number": "T123",
                    "ticket_status": "INPROGRESS",
                    "ticket_updated_time": "2020-12-02T12:33:12"
                },
                "sort": [
                    1606912392000
                ]
            }
        ]

相关问题