'ElasticSearch Aggregation Filter The results of the one filter buckets in accordance with the another filter buckets of the key

I want to query is similar to

select t1.plate_no,t1.cnt1,t2.cnt2 from 

(select plate_no,sum(pass_count) cnt1 from kdmotorvehicle where pass_time > '2022-04-01 03:00:00' group by plate_no having cnt1 >5 ) t1 
left join 
(select plate_no,sum(pass_count) cnt2 from kdmotorvehicle where pass_time < '2022-05-18 23:00:00' group by plate_no having cnt2 <10 ) t2 
on t1.plate_no = t2.plate_no

Want to achieve the business logic use dsl

Idea is: find out the corresponding polymerization, each in two filtering barrel, and then to filter results merging

{
  "size": 0,
  "aggs": {
    "before_filter": {
      "filter": {
        "range": {
          "pass_time": {
            "gte": "2022-04-06 00:00:00",
            "lte": "2022-04-10 23:00:00"
          }
        }
      },
      "aggs": {
        "group_by_plate_no_color": {
          "terms": {
            "script": {
              "source": "doc['plate_no'].value+','+doc['plate_color'].value",
              "lang": "painless"
            },
            "order": {
              "pass_count_total": "desc"
            },
            "size": 10000
          },
          "aggs": {
            "pass_count_total": {
              "sum": {
                "field": "pass_count"
              }
            },
            "having": {
              "bucket_selector": {
                "buckets_path": {
                  "cnt": "pass_count_total"
                },
                "script": {
                  "source": "params.cnt >= params.before_cnt",
                  "lang": "painless",
                  "params": {
                    "before_cnt": 10
                  }
                },
                "gap_policy": "skip"
              }
            }
          }
        }
      }
    },
    "after_filter": {
      "filter": {
        "range": {
          "pass_time": {
            "gte": "2022-04-11 00:00:00",
            "lte": "2022-04-16 23:00:00"
          }
        }
      },
      "aggs": {
        "group_by_plate_no_color": {
          "terms": {
            "script": {
              "source": "doc['plate_no'].value+','+doc['plate_color'].value",
              "lang": "painless"
            },
            "order": {
              "pass_count_total": "desc"
            },
            "size": 10000
          },
          "aggs": {
            "pass_count_total": {
              "sum": {
                "field": "pass_count"
              }
            },
            "having": {
              "bucket_selector": {
                "buckets_path": {
                  "cnt": "pass_count_total"
                },
                "script": {
                  "source": "params.cnt <= params.after_cnt",
                  "lang": "painless",
                  "params": {
                    "after_cnt": 1
                  }
                },
                "gap_policy": "skip"
              }
            }
          }
        }
      }
    }
  }
}

But, don't know how to speak two barrels to merge the result of the filter, tried to use pipe aggregation, but this can only be used on a filtering barrel

aggregation


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions