Alphabetically sorting + aggregation ElasticSearch

Elastic Stack Elasticsearch
1

Hi team,

I am using aggregation to get grouped records. I am not able to sort the buckets.

Dummy Data :

{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp001_local000000000000001",
    "_score": 10.0,
    "_source": {
        "name": [
            "Person 01"
        ],
        "groupbyid": [
            "group0001"
        ],
        "ranking": [
             "2.0"
        ]
    }
},
{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp002_local000000000000001",
    "_score": 85146.375,
    "_source": {
        "name": [
            "Person 02"
        ],
        "groupbyid": [
            "group0001"
        ],
        "ranking": [
             "10.0"
        ]
    }
},
{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp003_local000000000000001",
    "_score": 20.0,
    "_source": {
        "name": [
            "Person 03"
        ],
        "groupbyid": [
            "group0002"
        ],        
        "ranking": [
             "-1.0"
        ]
    }
},
{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp004_local000000000000001",
    "_score": 5.0,
    "_source": {
        "name": [
            "Person 04"
        ],
        "groupbyid": [
            "group0002"
        ],
        "ranking": [
             "2.0"
        ]
    }
}

Mapping:

{
    "name": {
        "type": "text",
        "fielddata": true,
        "fields": {
            "lower_case_sort": {
                "type": "text",
                "fielddata": true,
                "analyzer": "case_insensitive_sort"
            }
        }
    },
   "ranking": {
         "type": "float"
    },
    "groupbyid": {
        "type": "text",
        "fielddata": true,
        "index": "analyzed",
        "fields": {
            "raw": {
                "type": "keyword",
                "index": "not_analyzed"
            }
        }
    }
}

I wanted to sort by the name field. So if the Sort order is DESC, then I want the data like,

group0002
    emp003_local000000000000001
    emp004_local000000000000001

group0001
    emp001_local000000000000001
    emp002_local000000000000001

Explanation: group0002, will come first because the name Person 04 will came first by DESC order.

I have tried a couple of things, but I can not able to achieve for the String field. I am able to achieve for the integer field.

Thanks in Advance.

2

@elasticsearch -- Can anyone help on this?

3

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

4

Hi @dadoonet,

Thanks for the reply and apologies for not providing the query/script. Please find the query which I am hitting on elasticsearch.

{
  "from": 0,
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "name:XYZ"
          }
        }
      ]
    }
  },
  "aggregations": {
    "groupbyid": {
      "terms": {
        "field": "groupbyid.raw",
        "size": 100
      },
      "aggs": {
        "top_hit_agg": {
          "top_hits": {
            "size": 100
          }
        },
        "ranking_agg": {
            "min": {
                "field": "ranking"
            }
        },
        "test_bucket_sort": {
          "bucket_sort": {
            "sort": [
              {
                "ranking_agg": {
                  "order": "desc"
                }
              }
            ],
            "size": 100,
            "from": 0
          }
        }
      }
    }
  }
}

I am able to sort for the ranking field. Was there any way I can group on groupbyid field and sort on the name field?

Thanks

5

Could you share a full script we can just copy, paste and execute in Kibana console?

6

Hi @dadoonet,

I am using this query only to get the records. Just the query and data are not real. I am using the GET API of Elasticsearch via postman for now as a part of testing.

I have shared the request body. Do you need anything more as I already have shared mapping, dummy data, and a script?

7

A script that we can just copy and paste in kibana would help to reproduce the exact problem.

8

I could not share the exact data so have provided the dummy data and elasticsearch exact query which I am hitting.

9

Could you share a script with dummy data that we can just paste in Kibana dev console and then click on the play button to execute it?

10

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