How to sort buckets across nested bucket fields

Elastic Stack Elasticsearch
1

I'm unable to figure out how to write a query which will sort buckets based on their nested fields.

Each document in my index has this schema:

{
  "provider_message_text": "this is about a song",
  "sentiment_score": 0.4,
  "sentiment_magnitude": 0.35
}

I first group documents into buckets based on what they are about, then based on the sentiment score and magnitude, I group messages into three buckets: negative, positive and neutral. This is done with this query:

{
  "aggs": {
    "messages": {
      "filters": {
        "filters": {
          "song": {
            "bool": {
              "should": [
                {"match_phrase": {"provider_message_text": {"query": "song"}}}
              ]
            }
          },
          "help": {
            "bool": {
              "should": [
                {"match_phrase": {"provider_message_text": {"query": "help"}}}
              ]
            }
          }
        }
      },
      "aggs": {
        "sentiment": {
          "filters": {
            "other_bucket_key": "neutral",
            "filters": {
              "positive": {
                "bool": {
                  "must": [
                    {"range": {"sentiment_score": {"gte": 0.2}}},
                    {"range": {"sentiment_magnitude": {"gt": 0.3}}}
                  ]
                }
              },
              "negative": {
                "bool": {
                  "must": [
                    {"range": {"sentiment_score": {"lte": -0.2}}},
                    {"range": {"sentiment_magnitude": {"gt": 0.3}}}
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
}

Which produces the following results:

{
  "messages": {
    "buckets": {
      "help": {
        "doc_count": 5,
        "sentiment": {
          "buckets": {
            "negative": {"doc_count": 2},
            "positive": {"doc_count": 0},
            "neutral": {"doc_count": 3}
          }
        }
      },
      "song": {
        "doc_count": 15,
        "sentiment": {
          "buckets": {
            "negative": {"doc_count": 9},
            "positive": {"doc_count": 2},
            "neutral": {"doc_count": 4}
          }
        }
      }
    }
  }
}

I want to be able to sort the top level buckets (help and song in this case) based on their nested sentiment.buckets.negative.doc_count properties, so that I can see the bucket which has the highest number of negative documents first/last.

Can this be done in ES?

2

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