Sort terms aggregation by totla sum of 2 nested sum aggregations

[Assaf_Cohen]

I have an index which stores all the books and articles read by users. The books and articles are of nested type. I am trying to get the top x users who read the highest number of books and articles which match the specified filters.

I was able to get the total number of books read for each user (see aggregation below named books_total_reads)

I was also able to get the total number of articles read by each user (see aggregation below named articles_total_reads)

What i can't figure out is how to sort the the top x users by highest sum of: books_total_reads + articles_total_reads.

So in the example below, the query should return top 2 users in following order: john (total 23), alice (total 19)

To reproduce this you can run commands below:

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

PUT my-index/_doc/1
{
  "reader" : "mark",
  "books" : [
    {
      "genre" : "flowers",
      "count" :  3
    },
    {
      "genre" : "animals",
      "count" :  5
    }
  ],
  "articles" : [
    {
      "genre" : "news",
      "count" :  5
    },
    {
      "genre" : "finance",
      "count" :  5
    }
  ]
}

PUT my-index/_doc/2
{
  "reader" : "john",
  "books" : [
    {
      "genre" : "animals",
      "count" :  1
    }
  ],
  "articles" : [
    {
      "genre" : "ships",
      "count" :  20
    },
    {
      "genre" : "finance",
      "count" :  2
    }
  ]
}

PUT my-index/_doc/3
{
  "reader" : "alice",
  "books" : [
    {
      "genre" : "flowers",
      "count" :  4
    },
    {
      "genre" : "animals",
      "count" :  5
    }
  ],
  "articles" : [
    {
      "genre" : "news",
      "count" :  5
    },
    {
      "genre" : "finance",
      "count" :  5
    }
  ]
}


POST /my-index/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "books",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "books.genre": [ "flowers", "animals"]
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "articles",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "articles.genre": [ "news", "finance", "ships"]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "readers": {
      "terms": {
        "field": "reader",
        "size": 2
      },
      "aggs": {
        "books_root_agg": {
          "nested": {
            "path": "books"
          },
          "aggs": {
            "books": {
              "terms": {
                "field": "books.genre",
                "include": [ "flowers", "animals", "ships"],
                "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": [ "news", "finance", "ships"],
                "size": 10,
                "order": {
                  "sum_reads": "desc"
                }
              },
              "aggs": {
                "sum_reads": {
                  "sum": {
                    "field": "articles.count"
                  }
                }
              }
            },
            "articles_total_reads": {
              "sum_bucket": {
                "buckets_path": "articles>sum_reads"
              }
            }
          }
        }
      }
    }
  }
}

[Assaf_Cohen]

In case it makes a difference, I would also like to add that each genre has a name and id. In the examples above, for clarity, I filtered by genre names (i.e. flowers, animals, finance, ships etc'), but in reality I will filter by the genre Ids (which are numbers from 1 to 1000)

[RabBit_BR (andre.coelho)]

Hi @Assaf_Cohen

To sum the book_total_reads and article_total_reads fields, you can use a bucket_script aggregation.

{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "books",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "books.genre": [
                        "flowers",
                        "animals"
                      ]
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "articles",
            "query": {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "articles.genre": [
                        "news",
                        "finance",
                        "ships"
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "readers": {
      "terms": {
        "field": "reader",
        "size": 2
      },
      "aggs": {
        "books_root_agg": {
          "nested": {
            "path": "books"
          },
          "aggs": {
            "books": {
              "terms": {
                "field": "books.genre",
                "include": [
                  "flowers",
                  "animals",
                  "ships"
                ],
                "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": [
                  "news",
                  "finance",
                  "ships"
                ],
                "size": 10,
                "order": {
                  "sum_reads": "desc"
                }
              },
              "aggs": {
                "sum_reads": {
                  "sum": {
                    "field": "articles.count"
                  }
                }
              }
            },
            "articles_total_reads": {
              "sum_bucket": {
                "buckets_path": "articles>sum_reads"
              }
            }
          }
        },
        "total_reads_sum": {
          "bucket_script": {
            "buckets_path": {
              "booksTotalReads": "books_root_agg>books_total_reads",
              "articlesTotalReads": "articles_root_agg>articles_total_reads"
            },
            "script": "params.booksTotalReads + params.articlesTotalReads"
          }
        }
      }
    }
  }
}