How to get an Elasticsearch aggregation with multiple fields

Performing multi-field aggregation in Elasticsearch typically involves bucket aggregations, which group documents based on one or more fields and enable statistical calculations on these groups. Specifically, for multi-field aggregation, you can use multi-bucket aggregations such as terms and histogram aggregations, which can be nested to build complex aggregation structures.

Example Scenario

Assume we have an e-commerce platform recording user purchase records, where each record includes user ID, product category, and purchase amount. Now, we want to obtain the total spending per user per product category.

Elasticsearch Query Implementation

To achieve this requirement, we can first aggregate by user ID, then within each user's aggregation, aggregate by product category, and finally use the sum aggregation to calculate the total amount.

Below is the corresponding Elasticsearch query DSL (Domain Specific Language) example:

json
POST /purchases/_search
{
  "size": 0, 
  "aggs": {
    "by_user": {
      "terms": {
        "field": "user_id",
        "size": 10
      },
      "aggs": {
        "by_category": {
          "terms": {
            "field": "category",
            "size": 10
          },
          "aggs": {
            "total_spent": {
              "sum": {
                "field": "amount"
              }
            }
          }
        }
      }
    }
  }
}

Explanation

1. Top-level aggregation by_user: This aggregation groups all documents by the user_id field, with each user ID forming a bucket.
2. Second-level aggregation by_category: For documents within each user ID bucket, we aggregate again by the category field, with each product category forming a bucket.
3. Third-level aggregation total_spent: Within each product category bucket, we calculate the total spending by summing the amount field.

Summary

Through this nested aggregation approach, we can flexibly analyze and statistically process data across multiple dimensions to meet complex business requirements. Elasticsearch's powerful aggregation capabilities make handling large-scale data simple and efficient. In practical applications, adjust the aggregation fields and methods, as well as the granularity and scope, based on actual data and business needs.