Elasticsearch 7.1基于Composite Aggregation实现分组聚合后对聚合指标的排序分页问题求助

Hey there, I totally get your frustration—Composite aggregation is fantastic for paginating large volumes of grouped buckets, but as you've discovered, it only supports sorting by the grouping dimensions (Code1, Code2, Date1, Date2) defined in its sources array. It can't natively sort on computed metrics like TotalValue (sum of Value) or doc_count. Let's walk through two practical solutions tailored to your Elasticsearch 7.1 setup, depending on your real-time needs.

Option 1: Use Elasticsearch Transform (Best for Near-Real-Time Use Cases) #

Transform lets you pre-compute your aggregated results and store them in a dedicated index. Once the data is in this index, you can run standard search queries with full sorting/pagination support across all fields—including your metrics. This is the most scalable approach if you don't need up-to-the-second fresh data.

Step 1: Create the Transform #

Define a pivot transform that groups by your four fields and calculates the required metrics:

PUT _transform/aggregated-events-transform
{
  "source": {
    "index": "event"
  },
  "dest": {
    "index": "aggregated-events"
  },
  "pivot": {
    "group_by": {
      "Code1": { "terms": { "field": "Code1" } },
      "Code2": { "terms": { "field": "Code2" } },
      "Date1": { "terms": { "field": "Date1" } },
      "Date2": { "terms": { "field": "Date2" } }
    },
    "aggregations": {
      "TotalValue": { "sum": { "field": "Value" } },
      "Count": { "value_count": { "field": "Value" } }
    }
  },
  "sync": {
    "time": {
      "field": "Date1",
      "delay": "60s"
    }
  }
}

• value_count gives you the same result as doc_count (assuming every document has a Value field).
• The sync section keeps the aggregated index updated every 60 seconds (adjust the delay as needed).

Step 2: Start the Transform #

POST _transform/aggregated-events-transform/_start

Step 3: Query the Aggregated Index #

Now you can run a standard search with sorting and pagination that matches your desired output format:

GET aggregated-events/_search
{
  "size": 10,
  "from": 0,
  "sort": [
    { "TotalValue": "desc" }, // Sort by total value descending first
    { "Count": "asc" },       // Then by count ascending
    { "Code1": "desc" }       // Fallback to Code1 if needed
  ],
  "_source": ["Code1", "Code2", "Date1", "Date2", "TotalValue", "Count"]
}

This returns exactly the flat, structured JSON you're looking for, no post-processing required.

Option 2: Nested Terms + Bucket Sort (For Real-Time Results) #

If you need strictly real-time data, you can use nested Terms aggregations combined with Bucket Sort. Note: This approach loads all aggregation buckets into memory, so it's only feasible if the total number of unique (Code1+Code2+Date1+Date2) combinations is small to medium (avoid using this for millions of buckets).

Example Query #

GET event/_search
{
  "size": 0,
  "aggs": {
    "group_all": {
      "global": {},
      "aggs": {
        "group_code1": {
          "terms": {
            "field": "Code1",
            "size": 10000 // Set to a value larger than your unique Code1 count
          },
          "aggs": {
            "group_code2": {
              "terms": {
                "field": "Code2",
                "size": 10000
              },
              "aggs": {
                "group_date1": {
                  "terms": {
                    "field": "Date1",
                    "size": 10000
                  },
                  "aggs": {
                    "group_date2": {
                      "terms": {
                        "field": "Date2",
                        "size": 10000
                      },
                      "aggs": {
                        "TotalValue": { "sum": { "field": "Value" } },
                        "Count": { "value_count": { "field": "Value" } }
                      }
                    }
                  }
                }
              }
            }
          }
        },
        "sorted_buckets": {
          "bucket_sort": {
            "sort": [
              { "TotalValue": { "order": "desc" } },
              { "Count": { "order": "asc" } }
            ],
            "size": 10,
            "from": 0
          }
        }
      }
    }
  }
}

After running this query, you'll need to flatten the nested bucket structure in your client code to match your desired output format.

Key Takeaways #

• Transform is the recommended approach for most use cases: it's scalable, low-overhead, and lets you use standard search functionality for sorting/pagination.
• Use the nested Terms + Bucket Sort method only if you require real-time results and have a manageable number of unique bucket combinations.

内容的提问来源于stack exchange，提问作者bazzinga