How order aggregations using elasticsearch 7.15

[acantepie (Cantepie)]

Hello,

I have the following mapping :

"COLOR": {
      "properties": {
        "order": {
          "type": "long"
        },
        "value": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        }
      }
    },

"COLOR": {
    "value": "Rose",
    "order": 6
  },

How can i order value by value of order field on my search request ?

{
    query: ...,
   aggs: {
       terms: { field: COLOR.value.keyword, order: ???? )
   }

Thx for helping

PS: post edited

[dadoonet (David Pilato)]

You want to order by value (alphabetical sort on Rose...) or by count (10, 9, 9) or by the value of the order field?

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.

[acantepie (Cantepie)]

Thx for answer, i want to order by the value of the order field.
I will try to post a script toworrow, i am currently not using Kibana to manage ES.

[acantepie (Cantepie)]

Kibana script

POST test1/_doc/1
{
  "COLOR": {
    "value": "Rose",
    "order": 2
  }
}

POST test1/_doc/2
{
  "COLOR": {
    "value": "Jaune",
    "order": 1
  }
}

POST test1/_doc/3
{
  "COLOR": {
    "value": "Bleu",
    "order": 3
  }
}

GET test1/_search
{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "group_by_color": {
      "terms": {
        "field": "COLOR.value.keyword"
      } 
    }
  }
}

Result :

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "test1",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "COLOR" : {
            "value" : "Rose",
            "order" : 2
          }
        }
      },
      {
        "_index" : "test1",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "COLOR" : {
            "value" : "Jaune",
            "order" : 1
          }
        }
      },
      {
        "_index" : "test1",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "COLOR" : {
            "value" : "Bleu",
            "order" : 3
          }
        }
      }
    ]
  },
  "aggregations" : {
    "group_by_color" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Bleu",
          "doc_count" : 1
        },
        {
          "key" : "Jaune",
          "doc_count" : 1
        },
        {
          "key" : "Rose",
          "doc_count" : 1
        }
      ]
    }
  }
}

Wanted result :
On aggregations.group_by_color.buckets, i want my aggregation values ordered by COLOR.order

[dadoonet (David Pilato)]

You can do this:

GET test1/_search
{
  "size": 0, 
  "aggs": {
    "group_by_color": {
      "terms": {
        "field": "COLOR.value.keyword",
        "order": { "order": "desc" }
      },
      "aggs": {
        "order": {
          "max": {
            "field": "COLOR.order"
          }
        }
      }
    }
  }
}

It gives:

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "group_by_color" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Bleu",
          "doc_count" : 1,
          "order" : {
            "value" : 3.0
          }
        },
        {
          "key" : "Rose",
          "doc_count" : 1,
          "order" : {
            "value" : 2.0
          }
        },
        {
          "key" : "Jaune",
          "doc_count" : 1,
          "order" : {
            "value" : 1.0
          }
        }
      ]
    }
  }
}