Elasticsearch按created_by.name排序terms聚合桶问题求助
2026-7-7
Elasticsearch按关联字段对聚合桶排序的实现方案
需求与问题
需要按created_by.id字段进行terms聚合分组，同时将聚合桶按created_by.name降序排列（Tito对应的桶需排在Edwin之前），且聚合key保留created_by.id而非name。此前在top_hits中添加的sort仅对桶内文档生效，无法改变聚合桶的顺序。
原查询语句
{ "size" : 0, "from" : 0, "aggs": { "by_filter": { "filter": { "bool": { "must": [ { "range": { "published_at": { "gte": "2019-08-01 00:00:00", "lte": "2023-10-30 23:59:59" } } }, { "match": { "status": "published" } } ] } }, "aggs": { "by_created": { "terms": { "field": "created_by.id", "size": 10 }, "aggs" : { "count_data": { "terms": { "field": "created_by.id" } }, "hits": { "top_hits": { "sort": [ { "created_by.name.keyword": { "order": "desc" } } ], "_source":["created_by.name"], "size": 1 } } } } } } } }
当前返回结果
"aggregations": { "by_filter": { "doc_count": 21, "by_created": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 3, "buckets": [ { "key": 34, "doc_count": 3, "hits": { "hits": { "total": { "value": 3, "relation": "eq" }, "max_score": null, "hits": [ { "_index": "re_article", "_id": "53822", "_score": null, "_source": { "created_by": { "name": "Edwin" } }, "sort": [ "Edwin" ] } ] } }, "count_data": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": 34, "doc_count": 3 } ] } }, { "key": 52, "doc_count": 3, "hits": { "hits": { "total": { "value": 3, "relation": "eq" }, "max_score": null, "hits": [ { "_index": "re_article", "_id": "338610", "_score": null, "_source": { "created_by": { "name": "Tito" } }, "sort": [ "Tito" ] } ] } }, "count_data": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": 52, "doc_count": 3 } ] } } ] } } }
预期返回结果
"aggregations": { "by_filter": { "doc_count": 21, "by_created": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 3, "buckets": [ { "key": 52, "doc_count": 3, "hits": { "hits": { "total": { "value": 3, "relation": "eq" }, "max_score": null, "hits": [ { "_index": "re_article", "_id": "338610", "_score": null, "_source": { "created_by": { "name": "Tito" } } } ] } }, "count_data": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": 52, "doc_count": 3 } ] } }, { "key": 34, "doc_count": 3, "hits": { "hits": { "total": { "value": 3, "relation": "eq" }, "max_score": null, "hits": [ { "_index": "re_article", "_id": "53822", "_score": null, "_source": { "created_by": { "name": "Edwin" } } } ] } }, "count_data": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": 34, "doc_count": 3 } ] } } ] } } }
示例数据
{ "id": 53822, "created_at": "2019-09-03 18:17:13", "published_at": "2019-09-04 01:17:13", "status": "published", "created_by": { "id": 34, "name": "Edwin", "role_id": 4, "is_active": "Y" } }, { "id": 338610, "created_at": "2022-10-16 20:48:39", "published_at": "2022-10-16 21:08:12", "status": "published", "created_by": { "id": 52, "name": "Tito", "role_id": 4, "is_active": "Y" } }, { "id": 54272, "created_at": "2019-09-10 08:28:57", "published_at": "2019-09-10 15:30:03", "status": "published", "created_by": { "id": 34, "name": "Edwin", "role_id": 4, "is_active": "Y" } }
解决方案
要实现按关联字段对聚合桶排序，需要先在每个created_by.id的桶内提取对应的created_by.name值，再通过bucket_sort聚合对桶进行排序。
修改后的查询语句
{ "size": 0, "from": 0, "aggs": { "by_filter": { "filter": { "bool": { "must": [ { "range": { "published_at": { "gte": "2019-08-01 00:00:00", "lte": "2023-10-30 23:59:59" } } }, { "match": { "status": "published" } } ] } }, "aggs": { "by_created": { "terms": { "field": "created_by.id", "size": 10 }, "aggs": { "count_data": { "terms": { "field": "created_by.id" } }, "hits": { "top_hits": { "_source": ["created_by.name"], "size": 1 } }, // 提取当前桶对应的created_by.name（因id唯一，取第一个即可） "created_by_name": { "terms": { "field": "created_by.name.keyword", "size": 1 } }, // 按created_by_name的key降序排序桶 "sort_by_name": { "bucket_sort": { "sort": [ { "created_by_name>_key": { "order": "desc" } } ] } } } } } } } }
原理说明
- 提取关联字段：新增
created_by_name的terms聚合，从每个created_by.id的桶中获取对应的唯一created_by.name值（因为同一个id对应唯一name，size设为1即可）。 - 桶排序：通过
bucket_sort聚合，使用created_by_name>_key作为排序字段，对聚合桶进行降序排列，实现Tito的桶排在Edwin之前的需求。 - 保留原key：
by_created的terms聚合仍以created_by.id为key，符合需求。
内容的提问来源于stack exchange，提问作者Lidya
相关产品推荐
相关产品推荐
