How to do sorting on a field with composite aggregation in elastic search

Question

How to do sorting on a field with composite aggregation in elastic search.

We are using elastic search version 6.8.6 and trying to achieve sorting on a field with composite aggregation. But we are not able to get expected results with aggregation.

This is our mapping

{
  "properties": {
    "department": {
      "type": "text",
      "fields": {
        "keyword": {
          "ignore_above": 256.0,
          "type": "keyword"
        }
      }
    },      
    "project": {
      "type": "text",
      "fields": {
        "keyword": {
          "ignore_above": 256.0,
          "type": "keyword"
        }
      }
    },
    "billingUnit": {
      "type": "text",
      "fields": {
        "keyword": {
          "ignore_above": 256.0,
          "type": "keyword"
        }
      }
    },
    "billingType": {
      "type": "text",
      "fields": {
        "keyword": {
          "ignore_above": 256.0,
          "type": "keyword"
        }
      }
    },
    "application": {
      "type": "text",
      "fields": {
        "keyword": {
          "ignore_above": 256.0,
          "type": "keyword"
        }
      }
    },
    "environmet": {
      "type": "text",
      "fields": {
        "keyword": {
          "ignore_above": 256.0,
          "type": "keyword"
        }
      }
    },
    "cost": {
      "type": "float"
    }
  }
}

By using the following query we are not able to do sorting, The results are not in alphabetical orders :

{
  "query": {
    "bool": {
      "must": [
        {
          "match_phrase": {
            "department": {
              "query": "HR",
              "slop": 0,
              "zero_terms_query": "NONE",
              "boost": 1.0
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1.0
    }
  },
  "sort": [
    {
      "project.keyword": {
        "order": "desc"
      }
    }
  ],
  "aggs": {
    "TERM_RANGE": {
      "composite": {
        "size": 10000,
        "sources": [
          {
            "billingUnitKey": {
              "terms": {
                "field": "billingUnit.keyword",
                "missing_bucket": false
              }
            }
          },
          {
            "billingTypeKey": {
              "terms": {
                "field": "billingType.keyword",
                "missing_bucket": false
              }
            }
          }
        ]
      },
      "aggregations": {
        "TOTAL": {
          "sum": {
            "field": "cost"
          }
        },
        "dataHits": {
          "top_hits": {
            "from": 0,
            "size": 1,
            "version": false,
            "seq_no_primary_term": false,
            "explain": false,
            "_source": {
              "includes": [
                "application.keyword",
                "environmet.keyword",
               
              ],
              "excludes": []
            },
            "docvalue_fields": [
              {
                "field": "application.keyword"
              },
              {
                "field": "environmet.keyword"
              }
            ]
          }
        },
        "paginate_bucket": {
          "bucket_sort": {
            "sort": [],
            "from": 0,
            "size": 100,
            "gap_policy": "SKIP"
          }
        }
      }
    }
  }
}

Sorting is working fine with following query without aggregation

{
  "query": {
    "match": {
      "department": "HR"
    }
  },
  "size": 100,
  "sort": [
    {
      "project.keyword": {
        "order": "desc"
      }
    }
  ]
}

score 0 · Answer 1 · answered Nov 15 '20 at 13:10

0

You should use order key of composite aggregation https://www.elastic.co/guide/en/elasticsearch/reference/7.8/search-aggregations-bucket-composite-aggregation.html#_order

answered Nov 15 '20 at 13:10

drewblin

1
1

1

A link to a solution is welcome, but please ensure your answer is useful without it: add context around the link so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. Answers that are little more than a link may be deleted. – incarnadine Nov 15 '20 at 14:51

How to do sorting on a field with composite aggregation in elastic search

1 Answers1