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

我想得到这个结果，但我不知道如何获得限制大小，以及在哪里使用排序

json prettyprint-override">{
  "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

1. 弹性搜索通常不支持聚合分页，但是，复合聚合提供了一种对聚合分页的方法

与其他多桶聚合不同，复合聚合可用于有效地分页来自多级聚合的所有桶。

摘自复合聚合ES文档。

检查：这个

除了“ORDER BY first\U time desc”，下面的查询应该可以正常运行。我认为除了分组字段（src\u ip，threat\u target）之外，不可能在任何字段上排序。

 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"
   }
 }
 }
 }
 }
 }