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

Viewed 1426

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"
      }
    }
  ]
}
1 Answers
Related