SORTING, SUM, PAGINATION, and AGGREGATION all in one!

Elastic Stack Elasticsearch
1

'm using Elasticsearch and I see questions now and then about doing some aggregations with sorting or aggregations with paging, but I never see anything GROUP_BY, SUM, SORT, and PAGINATION together. If I were to write what I'm looking for as SQL, here it is (without the PAGINATION).

select invoice_date, address_2, company_name, sum(amount) 
from my_table 
group by invoice_date, address_2, company_name
order by sum(amount) desc

I tried doing this using many different techniques like composite aggregation, however it appears I can't do the ORDER_BY with this on the summation.

# composite aggregation
POST /746ee3a6-2b87-4288-9f20-3bf3a9e47e93/_search
{
  "size": 0,
  "aggs": {
    "my_buckets": {
      "composite": {
        "sources": [
          { "Address2": { "terms": { "field": "Address2" } } },
          { "Company_Description": { "terms": { "field": "Company_Description" } } },
          { "InvoiceDate": { "terms": { "field": "InvoiceDate" } } }
        ]
      },
      "aggregations": {
        "summation": {
          "sum": { "field": "GrossValue" }
        }
      }
    }
  }
}

I tried repeated nested aggregations but I saw a comment somewhere that with many nested levels you can't ORDER_BY either.

POST /746ee3a6-2b87-4288-9f20-3bf3a9e47e93/_search
{
  "size":0,
  "from":0,
  "sort":[{"Address2":"asc"}],
  "query":{"bool":{"must":[{"match":{"taxonomy_full_code":-1}}]}},
  "track_total_hits":true,
  "aggs":{
    "agg0":{
      "terms":{"field":"Address2"},
      "aggs":{
        "agg1":{
          "terms":{"field":"Company_Description"},
          "aggs":{
            "agg2":{
              "terms":{"field":"InvoiceDate"},
              "aggs":{"sum(GrossValue)":{"sum":{"field":"GrossValue"}}
              }
            }
          }
        }
      }
    }
  }
}

Same with multi-term aggregation

# multi-term aggregation
POST /746ee3a6-2b87-4288-9f20-3bf3a9e47e93/_search
{
  "size": 0,
  "aggs": {
    "rule_builder": {
      "multi_terms": {
        "terms": [
          {"field": "Address2"},
          {"field": "Company_Description"},
          {"field": "InvoiceDate"}
        ]
      },
      "aggs":{
        "sum(GrossValue)":{"sum":{"field":"GrossValue"}}
      }
    }
  }
}

I'm using Elasticsearch 7.14. Is what I'm looking for possible in this version?

2

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