# Sort terms aggregation by totla sum of 2 nested sum aggregations

**URL:** <https://discuss.elastic.co/t/sort-terms-aggregation-by-totla-sum-of-2-nested-sum-aggregations/351915>\
**Category:** Elasticsearch\
**Created:** [January 27, 2024, 12:11pm UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-totla-sum-of-2-nested-sum-aggregations/351915 "2024-01-27T12:11:07Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Assaf\_Cohen](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/assaf_cohen/32/46023_2.png) [@Assaf\_Cohen](https://discuss.elastic.co/u/Assaf_Cohen)\
**Post date:** [January 27, 2024, 12:11pm UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-totla-sum-of-2-nested-sum-aggregations/351915/1 "2024-01-27T12:11:07Z")

</div>

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:

```auto
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"
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Assaf\_Cohen](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/assaf_cohen/32/46023_2.png) [@Assaf\_Cohen](https://discuss.elastic.co/u/Assaf_Cohen)\
**Post date:** [January 28, 2024, 6:57am UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-totla-sum-of-2-nested-sum-aggregations/351915/2 "2024-01-28T06:57:33Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\
**Post date:** [January 28, 2024, 7:12pm UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-totla-sum-of-2-nested-sum-aggregations/351915/3 "2024-01-28T19:12:04Z")

</div>

Hi @Assaf_Cohen

To sum the book\_total\_reads and article\_total\_reads fields, you can use a [bucket\_script](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html) aggregation.

```auto
{
  "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"
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 25, 2024, 7:12pm UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-totla-sum-of-2-nested-sum-aggregations/351915/4 "2024-02-25T19:12:33Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.