elasticsearch - sql到es:在agg上获取限制页面和订单结果

Question

SELECT
    max( timestamp ) AS first_time,
    min( timestamp ) AS last_time,
    src_ip,
    threat_target ,
    count(*) as count
FROM
    traffic 
GROUP BY
    src_ip,
    threat_target

ORDER BY
  first_time desc

LIMIT 0 ,10

我想获得此结果，但我不知道如何获取 limit size以及在哪里使用 sort

{
  "size": 0,
  "aggregations": {
    "src_ip": {
      "aggregations": {
        "threat_target": {
          "aggregations": {
            "last_time": {
              "max": {
                "field": "`timestamp`"
              }
            },
            "first_time": {
              "min": {
                "field": "`timestamp`"
              }
            }
          },
          "terms": {
            "field": "threat_target.keyword"
          }
        }
      },
      "terms": {
        "field": "src_ip.keyword"
      }
   
    }
  }
}

Answer 1

Elastic Search通常不支持

Aggregation分页，但是复合聚合提供了一种分页聚合的方法。

Unlike the other multi-bucket aggregation the composite aggregation can be used to paginate all buckets from a multi-level aggregation efficiently.

摘自 Composite-Aggregation ES文档。
检查: THIS

除了“ORDER BY first_time desc”外，下面的查询应该可以很好地运行。我不认为除了分组字段(src_ip，
可能有威胁)。

 GET traffic/_search
 {
 "size": 0,
 "aggs": {
 "my_bucket": {
 "composite": {
 "size": 2,  //<=========== PAGE SIZE
 /*"after":{   // <========== INCLUDE THIS FROM Second request onwards, passing after_key of the last output here for next page
   "src_ip" : "1.2.3.5",
   "threat_target" : "T3"
 },*/
 "sources": [
   {

     "src_ip": {
       "terms": {
         "field": "source_ip",
         "order": "desc"
       }
     }
   },
   {
     "threat_target": {
       "terms": {
         "field": "threat_target"
       }
     }
   }
 ]
 },
"aggs": {
 "first_time": {
   "max": {
     "field": "first_time"
   }
 }
 }
 }
 }
 }