Elastic Search return object with sum aggregation-CodePudding

I am trying to get a list of the top 100 guests by revenue generated with Elastic Search. To do this I am using a terms and a sum aggregation. However it does return the correct values, I wan to return the entire guest object with the aggregation.

This is my query:

GET reservations/_search
{
  "size": 0, 
  "aggs": {
    "top_revenue": {
      "terms": {
        "field": "total",
        "size": 100,
        "order": {
          "top_revenue_hits": "desc"
        }
      },
      "aggs": {
        "top_revenue_sum": {
          "sum": {
            "field": "total"
          }
        }
      }
    }
  }
}

This returns a list of the top 100 guests but only the amount they spent:

{
  "aggregations" : {
    "top_revenue" : {
      "doc_count_error_upper_bound" : -1,
      "sum_other_doc_count" : 498,
      "buckets" : [
        {
          "key" : 934.9500122070312,
          "doc_count" : 8,
          "top_revenue_hits" : {
          "value" : 7479.60009765625
          }
        },
        {
          "key" : 922.0,
          "doc_count" : 6,
          "top_revenue_hits" : {
          "value" : 5532.0
          }
        },
        ...
      ]
    }
  }
}

How can I get the query to return the entire guests object, not only the sum amount.

When I run GET reservations/_search it returns:

{
  "hits": [
    {
      "_index": "reservations",
      "_id": "1334620",
      "_score": 1.0,
      "_source": {
        "id": "1334620",
        "total": 110.8,
        "payment": "unpaid",
        "contact": {
          "name": "John Doe",
          "email": "[email protected]"
        }
      }
    },
    ... other reservations
  ]
}

I want to get this to return with the sum aggregation.

I have tried to use a top_hits aggregation, using _source it does return the entire guest object but it does not show the total amount spent. And when adding _source to the sum aggregation it gives an error.

Can I return the entire guest object with a sum aggregation or is this not the correct way?

CodePudding user response：

I assumed that contact.name is keyword in the mapping. Following query should work for you.

{
  "size": 0,
  "aggs": {
    "guests": {
      "terms": {
        "field": "contact.name",
        "size": 100
      },
      "aggs": {
        "sum_total": {
          "sum": {
            "field": "total"
          }
        },
        "sortBy": {
          "bucket_sort": {
            "sort": [
              { "sum_total": { "order": "desc" } } 
            ]
          }
        },
        "guest": {
          "top_hits": {
            "size": 1
          }
        }
      }
    }
  }
}