如何按书籍与文章阅读总量排序Elasticsearch用户聚合结果

我有一个存储用户阅读书籍和文章记录的Elasticsearch索引，其中books和articles字段为nested类型。需要获取符合指定genre过滤条件的、书籍与文章阅读总量最高的Top X用户。

目前已统计出每个用户符合条件的书籍阅读总量（对应聚合books_total_reads）和文章阅读总量（对应聚合articles_total_reads），但无法按两者之和对用户聚合结果排序。

示例数据中，查询应按以下顺序返回Top 2用户：john（总量23）、alice（总量19）。

复现场景代码 #

创建索引及映射 #

PUT my-index
{
  "mappings": {
    "properties": {
      "user": {
        "type": "keyword"
      },
      "books": {
        "type": "nested",
        "properties": {
          "genre": { "type": "keyword"},
          "count": { "type": "integer" }
        }
      },
      "articles": {
        "type": "nested",
        "properties": {
          "genre": { "type": "keyword"},
          "count": { "type": "integer" }
        }
      }
    }
  }
}

导入测试数据 #

PUT my-index/_doc/1
{
  "user" : "mark",
  "books" : [
    {
      "genre" : "1",
      "count" :  3
    },
    {
      "genre" : "2",
      "count" :  5
    }
  ],
  "articles" : [
    {
      "genre" : "10",
      "count" :  5
    },
    {
      "genre" : "11",
      "count" :  5
    }
  ]
}

PUT my-index/_doc/2
{
  "user" : "john",
  "books" : [
    {
      "genre" : "1",
      "count" :  1
    }
  ],
  "articles" : [
    {
      "genre" : "10",
      "count" :  2
    },
    {
      "genre" : "12",
      "count" :  20
    }
  ]
}

PUT my-index/_doc/3
{
  "user" : "alice",
  "books" : [
    {
      "genre" : "1",
      "count" :  4
    },
    {
      "genre" : "2",
      "count" :  5
    }
  ],
  "articles" : [
    {
      "genre" : "10",
      "count" :  5
    },
    {
      "genre" : "11",
      "count" :  5
    }
  ]
}

原查询（无法按总量排序） #

POST /my-index/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "books",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "books.genre": [
                        "1",
                        "2"
                      ]
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "articles",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "articles.genre": [
                        "10",
                        "11",
                        "12"
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "users": {
      "terms": {
        "field": "user",
        "size": 2
      },
      "aggs": {
        "books_root_agg": {
          "nested": {
            "path": "books"
          },
          "aggs": {
            "books": {
              "terms": {
                "field": "books.genre",
                "include": [
                  "1",
                  "2"
                ],
                "size": 10,
                "order": {
                  "sum_reads": "desc"
                }
              },
              "aggs": {
                "sum_reads": {
                  "sum": {
                    "field": "books.count"
                  }
                }
              }
            },
            "books_total_reads": {
              "sum_bucket": {
                "buckets_path": "books>sum_reads"
              }
            }
          }
        },
        "articles_root_agg": {
          "nested": {
            "path": "articles"
          },
          "aggs": {
            "articles": {
              "terms": {
                "field": "articles.genre",
                "include": [
                  "10",
                  "11",
                  "12"
                ],
                "size": 10,
                "order": {
                  "sum_reads": "desc"
                }
              },
              "aggs": {
                "sum_reads": {
                  "sum": {
                    "field": "articles.count"
                  }
                }
              }
            },
            "articles_total_reads": {
              "sum_bucket": {
                "buckets_path": "articles>sum_reads"
              }
            }
          }
        }
      }
    }
  }
}

要实现按总阅读量排序，需要在用户聚合下添加bucket_script计算总量，并将其作为排序依据。修改后的查询如下：

POST /my-index/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "books",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "books.genre": ["1", "2"]
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "articles",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "articles.genre": ["10", "11", "12"]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "users": {
      "terms": {
        "field": "user",
        "size": 2,
        "order": {
          "total_reads": "desc" // 按总阅读量降序排序
        }
      },
      "aggs": {
        "books_root_agg": {
          "nested": { "path": "books" },
          "aggs": {
            "books": {
              "terms": {
                "field": "books.genre",
                "include": ["1", "2"],
                "size": 10
              },
              "aggs": {
                "sum_reads": { "sum": { "field": "books.count" } }
              }
            },
            "books_total_reads": {
              "sum_bucket": { "buckets_path": "books>sum_reads" }
            }
          }
        },
        "articles_root_agg": {
          "nested": { "path": "articles" },
          "aggs": {
            "articles": {
              "terms": {
                "field": "articles.genre",
                "include": ["10", "11", "12"],
                "size": 10
              },
              "aggs": {
                "sum_reads": { "sum": { "field": "articles.count" } }
              }
            },
            "articles_total_reads": {
              "sum_bucket": { "buckets_path": "articles>sum_reads" }
            }
          }
        },
        // 新增：计算书籍和文章阅读总量之和
        "total_reads": {
          "bucket_script": {
            "buckets_path": {
              "books": "books_root_agg>books_total_reads",
              "articles": "articles_root_agg>articles_total_reads"
            },
            "script": "params.books + params.articles"
          }
        }
      }
    }
  }
}

关键修改说明 #

1. 添加total_reads聚合：使用bucket_script将books_total_reads和articles_total_reads相加，得到每个用户的总阅读量。
2. 修改用户聚合排序规则：在users的terms聚合中，将order设置为{"total_reads": "desc"}，确保按总阅读量从高到低排序。
3. 保留原有过滤逻辑：嵌套字段的genre过滤和聚合逻辑不变，保证只统计符合条件的阅读量。

执行该查询后，会返回预期的Top 2用户：john（23）、alice（19）。

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