elasticsearch sort aggregation on categorical value

Question

In elasticsearch, I can aggregate and sort the aggregation on a second aggregation's numeric field.

e.g.

GET myindex/_search
{
  "size":0,
  "aggs": {
    "a1": {
      "terms": { 
        "field": "FIELD1",
        "size":0,
        "order": {"a2": "desc"}  
      },
      "aggs":{
        "a2":{
          "sum":{
            "field":"FIELD2"
          }
        }
      }
    }
  }
}

However, I want to sort the aggregation on a categorical field value. ie. let's say the value of FIELD2 was one of ("a", "b", "c") -- I want to sort a1 first by all documents's with FIELD2: "a", then FIELD2: "b", then FIELD2: "c".

In my case, every FIELD1 has a unique FIELD2. So I really just want a way to sort the a1 results by FIELD2.

Answer 1

I am not sure what exactly you want but I tried following. I created index with mapping

PUT your_index
{
  "mappings": {
    "your_type": {
      "properties": {
        "name": {
          "type":   "string"
        },
        "fruit" : {"type" : "string", "index": "not_analyzed"}
      }
    }
  }
}

Then I indexed few documents like this

PUT your_index/your_type/1
{
  "name" : "federer",
  "fruit" : "orange"
}

Then I sorted all players with fruits with following aggregation

{
  "size": 0,
  "aggs": {
    "a1": {
      "terms": {
        "field": "name",
        "order": {
          "_term": "asc"
        }
      },
      "aggs": {
        "a2": {
          "terms": {
            "field": "fruit",
            "order": {
              "_term": "asc"
            }
          }
        }
      }
    }
  }
}

The result I got is

"aggregations": {
      "a1": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "federer",
               "doc_count": 3,
               "a2": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "green apple",
                        "doc_count": 1
                     },
                     {
                        "key": "orange",
                        "doc_count": 2
                     }
                  ]
               }
            },
            {
               "key": "messi",
               "doc_count": 2,
               "a2": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "apple",
                        "doc_count": 1
                     },
                     {
                        "key": "banana",
                        "doc_count": 1
                     }
                  ]
               }
            },
            {
               "key": "nadal",
               "doc_count": 2,
               "a2": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "blueberry",
                        "doc_count": 1
                     },
                     {
                        "key": "watermelon",
                        "doc_count": 1
                     }
                  ]
               }
            },
            {
               "key": "ronaldo",
               "doc_count": 2,
               "a2": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "banana",
                        "doc_count": 1
                     },
                     {
                        "key": "watermelon",
                        "doc_count": 1
                     }
                  ]
               }
            }
         ]
      }
   }

Make sure your FIELD2 is not_analyzed or you will get unexpected results.

Does this help?

Answer 2

I found a way that works. You must first aggregate on FIELD2, then on FIELD1.

    {
      "size": 0,
      "aggs": {
        "a2": {
          "terms": {
            "size": 0,
            "field": "FIELD2",
            "order": {
              "_term": "asc"
            }
          },
          "aggs": {
            "a1": {
              "terms": {
                "size": 0,
                "field": "FIELD1",
                "order": {
                  "_term": "asc"
                }
              }
            }
          }
        }
      }
    }