Elasticsearch能否基于子聚合结果过滤聚合？

需求背景 #

在交易索引中，我们需要筛选出总余额非零的账户（总余额=贷方总和-借方总和），希望直接在Elasticsearch查询阶段完成过滤，避免客户端遍历所有聚合结果再做筛选。

交易数据的_source结构示例：

"_source": {
  "debit": 50,
  "account_id": "account-id-1"
}

"_source": {
  "credit": 50,
  "account_id": "account-id-1"
}

"_source": {
  "debit": 25,
  "account_id": "account-id-2"
}

"_source": {
  "credit": 50,
  "account_id": "account-id-2"
}

方案一：保留借贷明细，过滤余额为零的账户 #

通过bucket_selector聚合，可以基于子聚合的结果对父terms聚合的bucket进行过滤。在原有聚合基础上，添加一个bucket_selector判断sum_credit和sum_debit是否相等，直接排除余额为零的账户bucket。

最终查询DSL #

{
  "size": 0,
  "aggs": {
    "terms_account_id": {
      "terms": {
        "field": "account_id"
      },
      "aggs": {
        "sum_debit": {
          "sum": {
            "field": "debit"
          }
        },
        "sum_credit": {
          "sum": {
            "field": "credit"
          }
        },
        "filter_non_zero_balance": {
          "bucket_selector": {
            "buckets_path": {
              "debitTotal": "sum_debit",
              "creditTotal": "sum_credit"
            },
            "script": "params.debitTotal != params.creditTotal"
          }
        }
      }
    }
  }
}

返回结果 #

"aggregations": {
    "terms_account_id": {
      "buckets": [
        {
          "key": "account-id-2",
          "sum_credit": { "value": 50 },
          "sum_debit": { "value": 25 }
        }
      ]
    }
  }

方案二：直接计算总余额并过滤 #

如果只需要总余额结果，可以先通过bucket_script计算出账户的总余额（贷方总和-借方总和），再用bucket_selector过滤掉余额为0的账户。

最终查询DSL #

{
  "size": 0,
  "aggs": {
    "terms_account_id": {
      "terms": {
        "field": "account_id"
      },
      "aggs": {
        "sum_debit": {
          "sum": {
            "field": "debit"
          }
        },
        "sum_credit": {
          "sum": {
            "field": "credit"
          }
        },
        "sum_total": {
          "bucket_script": {
            "buckets_path": {
              "debit": "sum_debit",
              "credit": "sum_credit"
            },
            "script": "params.credit - params.debit"
          }
        },
        "filter_non_zero_balance": {
          "bucket_selector": {
            "buckets_path": {
              "total": "sum_total"
            },
            "script": "params.total != 0"
          }
        }
      }
    }
  }
}

返回结果 #

"aggregations": {
    "terms_account_id": {
      "buckets": [
        {
          "key": "account-id-2",
          "sum_total": { "value": 25 }
        }
      ]
    }
  }

关键说明 #

bucket_selector是实现基于子聚合结果过滤的核心，它允许引用子聚合的计算值，通过脚本逻辑筛选符合条件的bucket，完全在Elasticsearch服务端完成过滤，减少了客户端的数据传输和处理开销。

内容的提问来源于stack exchange，提问作者cal macconnachie