Sort Aggregated Buckets From Nested Object Array By Specific Field-CodePudding

I have indexed documents such as

// doc 1
{
  ...,
  "list": [{
    "value": "a",
    "order": 1
  }, {
    "value": "b",
    "order": 2
  }]
  ,...
}
// doc 2
{
  ...,
  "list": [{
    "value": "b",
    "order": 2
  }, {
    "value": "c",
    "order": 3
  }]
  ,...
}

If I use the aggregation on the list.value:

{
  "aggs": {
    "values": {
      "terms": {
        "field": "list.value.keyword"
      }
    }
  }
}

I get buckets in order b, a, c:

"buckets" : [
        {
          "key" : "b",
          "doc_count" : 2
        },
        {
          "key" : "a",
          "doc_count" : 1
        },
        {
          "key" : "c",
          "doc_count" : 1
        }
      ]

as keys would be sorted by the _count in desc order.

If I use the aggregation on the list.value with sub-aggregation for sorting in form of max(list.order):

{
  "aggs": {
    "values": {
      "terms": {
        "field": "list.value.keyword",
        "order": { "max_order": "desc" }
      },
      "aggs": {
        "max_order": { "max": { "field": "list.order" } }
      }
    }
  }
}

I get buckets in order b, c, a

"buckets" : [
        {
          "key" : "b",
          "doc_count" : 2,
          "max_order" : {
            "value" : 3.0
          }
        },
        {
          "key" : "c",
          "doc_count" : 1,
          "max_order" : {
            "value" : 3.0
          }
        },
        {
          "key" : "a",
          "doc_count" : 1,
          "max_order" : {
            "value" : 2.0
          }
        }
      ]

as both b and c have max order 3 in their lists of the object.

However, I want to write a query to get buckets in order c, b, a as their order is 3, 2, 1 respectively. How to achieve that?

CodePudding user response：

You need to use nested aggregation, to get the buckets in order of c,b,a

Adding a working example with index data, mapping, search query and search result

Index Mapping

PUT testidx1
{
  "mappings":{
    "properties": {
      "list":{
        "type": "nested"
      }
    }
  }
}

Index Data:

POST testidx1/_doc/1
{
  "list": [
    {
      "value": "a",
      "order": 1
    },
    {
      "value": "b",
      "order": 2
    }
  ]
}

POST testidx1/_doc/2
{
  "list": [
    {
      "value": "b",
      "order": 2
    },
    {
      "value": "c",
      "order": 3
    }
  ]
}

Search Query:

POST testidx1/_search
{
  "size": 0,
  "aggs": {
    "resellers": {
      "nested": {
        "path": "list"
      },
      "aggs": {
        "unique_values": {
          "terms": {
            "field": "list.value.keyword",
            "order": {
              "max_order": "desc"
            }
          },
          "aggs": {
            "max_order": {
              "max": {
                "field": "list.order"
              }
            }
          }
        }
      }
    }
  }
}

Search Response:

"aggregations" : {
    "resellers" : {
      "doc_count" : 4,
      "unique_values" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "c",
            "doc_count" : 1,
            "max_order" : {
              "value" : 3.0
            }
          },
          {
            "key" : "b",
            "doc_count" : 2,
            "max_order" : {
              "value" : 2.0
            }
          },
          {
            "key" : "a",
            "doc_count" : 1,
            "max_order" : {
              "value" : 1.0
            }
          }
        ]
      }
    }
  }
}