elasticsearch - 弹性查询以获取缺少相应对的事件

Question

我有跟随生命周期的交易记录。

收到交易时的事件[RCVD]

事务待执行时发生事件[PNDG](可选步骤)

执行时的事件[SENT]

以下是索引中的7个示例事件:

{trxID: 1, status:RCVD}
{trxID: 2, status:RCVD}
{trxID: 3, status:RCVD}
{trxID: 2, status:PNDG}
{trxID: 3, status:PNDG}
{trxID: 1, status:SENT}
{trxID: 2, status:SENT}

我需要找到所有进入待处理状态但尚未执行的事务。换句话说，交易的状态应为 PNDG ，而不是 SENT 。
我试图在Java层不这样做。

我对trxID进行了汇总，然后对状态进行了子汇总。
然后，我无法弄清楚在子聚合中存储桶中只有PNDG的那些记录。我不确定我是否在朝正确的方向思考。

我期望的结果是 trxID 3 ，因为对于此事务，我们获得了 PNDG 状态，但尚未获得 SENT 。另一方面，不应报告 TrxUD 1 ，因为它从未进入 PNDG (待定)状态，无论是否报告 SENT的状态都没有。

Answer 1

您可以在交易ID下使用状态计数。

GET index24/_search
{
  "size": 0,
  "aggs": {
    "transactionId": {
      "terms": {
        "field": "trxID",
        "size": 10
      },
      "aggs": {
        "status": {
          "terms": {
            "field": "status.keyword",
            "size": 10
          }
        },
        "count": {
          "cardinality": {
            "field": "status.keyword"
          }
        },
        "my_bucketselector": {
          "bucket_selector": {
            "buckets_path": {
              "statusCount": "count"
            },
            "script": "params.statusCount==1"
          }
        }
      }
    }
  }
}

响应:

"aggregations" : {
    "transactionId" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 4,
          "doc_count" : 1,
          "count" : {
            "value" : 1
          },
          "status" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "PNDG",
                "doc_count" : 1
              }
            ]
          }
        }
      ]
    }
  }

编辑1:
我尝试了以下:-
获取交易ID的最大日期，然后获取未决的日期。如果两个日期相同，则最后一个状态为待定

数据:

 [
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "aYCs0m0BD5PlkoxXxO36",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 1,
          "status" : "RCVD",
          "date" : "2019-10-15T12:00:00"
        }
      },
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "aoCs0m0BD5PlkoxX7e35",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 1,
          "status" : "PNDG",
          "date" : "2019-10-15T12:01:00"
        }
      },
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "a4Ct0m0BD5PlkoxXCO06",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 1,
          "status" : "SENT",
          "date" : "2019-10-15T12:02:00"
        }
      },
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "bICt0m0BD5PlkoxXQe0Y",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 2,
          "status" : "RCVD",
          "date" : "2019-10-15T12:00:00"
        }
      },
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "bYCt0m0BD5PlkoxXZO2x",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 2,
          "status" : "PNDG",
          "date" : "2019-10-15T12:01:00"
        }
      },
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "boCt0m0BD5PlkoxXju1H",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 3,
          "status" : "RCVD",
          "date" : "2019-10-15T12:00:00"
        }
      },
      {
        "_index" : "index24",
        "_type" : "_doc",
        "_id" : "b4Ct0m0BD5PlkoxXou0-",
        "_score" : 1.0,
        "_source" : {
          "trxID" : 3,
          "status" : "SENT",
          "date" : "2019-10-15T12:01:00"
        }
      }
    ]

查询:

GET index24/_search
{
  "size": 0,
  "aggs": {
    "transactionId": {
      "terms": {
        "field": "trxID",
        "size": 10000
      },
      "aggs": {
        "maxDate": {
          "max": {
            "field": "date"  ---> get max date under transactions
          }
        },
        "pending_status": {
          "filter": {
            "term": {
              "status.keyword": "PNDG" ---> filter for pending
            }
          },
          "aggs": {
            "filtered_maxdate": {
              "max": {
                "field": "date"    --> get date under pending
              }
            }
          }
        },
        "buckets_latest_status_pending": { -->filter if max date==pending date
          "bucket_selector": {
            "buckets_path": {
              "filtereddate": "pending_status>filtered_maxdate",
              "maxDate": "maxDate"
            },
            "script": "params.filtereddate==params.maxDate"
          }
        }
      }
    }
  }
}

响应:

{
    "transactionId" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 2,  --> only transaction id 2 is returned
          "doc_count" : 2,
          "pending_status" : {
            "doc_count" : 1,
            "filtered_maxdate" : {
              "value" : 1.57114086E12,
              "value_as_string" : "2019-10-15T12:01:00.000Z"
            }
          },
          "maxDate" : {
            "value" : 1.57114086E12,
            "value_as_string" : "2019-10-15T12:01:00.000Z"
          }
        }
      ]
    }
  }