Sorted Pagination on Composite Aggregation

Elastic Stack Elasticsearch
1

I have ElasticSearch 7.1 documents with following mappings:-

{
  "event" : {
    "mappings" : {
      "properties" : {
        "Code1" : {
          "type" : "keyword"
        },
        "Code2" : {
          "type" : "keyword"
        },
        "Date1" : {
          "type" : "date"
        },
        "Date2" : {
          "type" : "date"
        },
        "Value" : {
          "type" : "long"
        }
      }
    }
  }
}

I want to group the documents by Code1, Code2, Date1, Date2 into buckets
together with

TotalValue which is sum of Value field of all documents in a bucket

and

Count which is number of documents in a bucket.

Final Output which I want is like this:-

{
    {
        "Code1": "ABC",
        "Code2": "XYZ",
        "Date1": "01/01/2022",
        "Date2": "31/01/2022",
        "TotalValue": "100",
        "Count": "3"
    },
    ...
}

Also I want, paginated output with sorting on any of the output fields of the bucket, viz. ; Code1, Code2, Date1, Date2, TotalValue, Count.

Using Composite Aggregation, I came up with this query, which is able to do aggregation as reqd with paginated response and sorting on Code1, Code2, Date1, Date2

but not able to do proper sorted pagination on TotalValueand Count(doc_count) fields.

GET event/_search
{
  "size":0,
  "aggs": {
      "AggregatedBucket": {
        "composite": {
          "size":"10",
          "sources": [
           {
              "Code1": {
                "terms": {
                  "field": "Code1",
                  "order": "desc"
                }
              }
            },
           {
              "Code2": {
                "terms": {
                  "field": "Code2",
                  "order": "desc"
                }
              }
            },
            {
              "Date1": {
                "terms": {
                  "field": "Date1",
                  "order": "desc"
                }
              }
            },
            {
              "Date2": {
                "terms": {
                  "field": "Date2",
                  "order": "desc"
                }
              }
            }
          ]
        },
        "aggs":{
            "TotalValue":{
              "sum": {
                "field": "Value"
              }
            }
        }
      }
    }
}}

Here is the truncated response I am getting

  "aggregations" : {
    "AggregatedBucket" : {
      "after_key" : {
        "Code1" : "ABC2",
        "Code2" : "XYZ2",
        "Date1" : "02/01/2022",
        "Date2" : "02/02/2022"
      },
      "buckets" : [
        {
          "key" : {
            "Code1" : "ABC1",
            "Code2" : "XYZ1",
            "Date1" : "01/01/2022",
            "Date2" : "01/02/2022"
          },
          "doc_count" : 1,
          "TotalValue" : {
            "value" : 4.0
          }
        },
        {
          "key" : {
            "Code1" : "ABC2",
            "Code2" : "XYZ2",
            "Date1" : "02/01/2022",
            "Date2" : "02/02/2022"
          },
          "doc_count" : 1,
          "TotalValue" : {
            "value" : 3.0
          }
        }
     ]
   }
 }

Any alternate way to return my expected response would also be helpful.

2

ElasticSearch 7.1 is EOL and no longer supported. Please upgrade ASAP.

(This is an automated response from your friendly Elastic bot. Please report this post if you have any suggestions or concerns :elasticheart: )

3

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