elasticsearch – Nest Sum aggregation with sub terms aggregation

I created aggregations includes a terms aggregate with a sum aggregate for each term bucket and will sort on the sum value. This works fine. However if I add a nested terms aggregation which is the second commented block in the code snippet. It fails with this error message “{“type”:”aggregation_execution_exception”,”reason”:”Invalid aggregation order path [AggCPUTimeMS].}” and after I remove the “Order” line it works just fine. Is it not possible to sort the bucket by a sum if there is sub aggregation?

Another question is about the first commented block: what is the difference between “Order” and “BucketSort”? My guess is “Order” will sort the buckets and pick the top n while “BucketSort” will sort the buckets already picked, randomly or not.

  var response = client.Search<SearchResultBucket>(search => search
                   .Size(0)
                   .RequestConfiguration(r => r.DisableDirectStreaming())
                   .Aggregations(aggContainer => aggContainer
                       .Terms("topLevelAggregation", termsAgg => termsAgg
                           .Field(new Field("System.keyword"))
                           .Size(5)
                           .Aggregations(aggContainer => aggContainer
                               .Sum("AggCPUTimeMS", sumAgg => sumAgg
                                   .Field(new Field("CpuTimeMilliseconds"))
                               )
                               //.BucketSort("sum_bucket_sort", bs => bs
                               //  .Sort(s => s
                               //      .Descending("AggCPUTimeMS")
                               //  )
                               //)
                           )
                           .Order(o => o.Descending("AggCPUTimeMS"))
                           //.Aggregations(aggContainer => aggContainer
                           //  .Terms("SubAggregation1", termsAgg => termsAgg
                           //      .Field(new Field("QueryHash.keyword"))
                           //  )
                           //)
                       )
                   )

Map here:

{
  "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043" : {
    "mappings" : {
      "dynamic_templates" : [
        {
          "message_field" : {
            "path_match" : "message",
            "match_mapping_type" : "string",
            "mapping" : {
              "norms" : false,
              "type" : "text"
            }
          }
        },
        {
          "string_fields" : {
            "match" : "*",
            "match_mapping_type" : "string",
            "mapping" : {
              "fields" : {
                "keyword" : {
                  "ignore_above" : 256,
                  "type" : "keyword"
                }
              },
              "norms" : false,
              "type" : "text"
            }
          }
        }
      ],
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "@version" : {
          "type" : "keyword"
        },
        "BatchResult" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientAppName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientHostname" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ClientProcessId" : {
          "type" : "long"
        },
        "CollectSystemDateUtc" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "CollectSystemTimeUtc" : {
          "type" : "date"
        },
        "CpuTimeMilliseconds" : {
          "type" : "long"
        },
        "CurrentVersion" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "DatabaseName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "Duration" : {
          "type" : "long"
        },
        "EventName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ExeDate" : {
          "type" : "date"
        },
        "IncrementalId" : {
          "type" : "long"
        },
        "LogicalReads" : {
          "type" : "long"
        },
        "Owner" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "OwnerId" : {
          "type" : "long"
        },
        "PhysicalReads" : {
          "type" : "long"
        },
        "Qty" : {
          "type" : "long"
        },
        "QueryHash" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryPlanHash" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryType" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "QueryTypeId" : {
          "type" : "long"
        },
        "ResourceGroupID" : {
          "type" : "long"
        },
        "ResourcePoolID" : {
          "type" : "long"
        },
        "RowCount" : {
          "type" : "long"
        },
        "ServerInstanceName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "ServerPrincipalName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SessionID" : {
          "type" : "long"
        },
        "SourceTableName" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SqlText" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 32766
            }
          },
          "norms" : false
        },
        "System" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          },
          "norms" : false
        },
        "SystemId" : {
          "type" : "long"
        },
        "TaskTime" : {
          "type" : "long"
        },
        "Writes" : {
          "type" : "long"
        },
        "geoip" : {
          "dynamic" : "true",
          "properties" : {
            "ip" : {
              "type" : "ip"
            },
            "latitude" : {
              "type" : "half_float"
            },
            "location" : {
              "type" : "geo_point"
            },
            "longitude" : {
              "type" : "half_float"
            }
          }
        }
      }
    }
  }
}

document example:

{
  "_index": "idx-au2-prod-sqlcpumonitoring-prod-2022.06.17-000043",
  "_id": "lDoxiYEBssAtKosd_uKJM",
  "_version": 1,
  "_score": 1,
  "_source": {
    "QueryType": "Regular",
    "SqlText": "BACKUP LOG @DbName TO DISK = @FilePath WITH INIT, NOSKIP, RETAINDAYS=1, NAME = 'LogBackup', NO_COMPRESSION;nttt",
    "LogicalReads": 417,
    "QueryTypeId": 1,
    "QueryHash": "0",
    "CollectSystemDateUtc": "2022.06.22",
    "DatabaseName": "FO8PRD",
    "ResourceGroupID": 280,
    "System": "Unknown",
    "SessionID": 3027,
    "@version": "1",
    "Writes": 8,
    "ClientAppName": "ServiceRunner",
    "ClientProcessId": 40792,
    "Owner": "CORE",
    "CollectSystemTimeUtc": "2022-06-22T02:02:50.293",
    "QueryPlanHash": "0",
    "Duration": 1133,
    "SystemId": 14,
    "Qty": 1,
    "@timestamp": "2022-06-22T02:15:45.711Z",
    "CpuTimeMilliseconds": 16,
    "EventName": "sp_statement_completed",
    "PhysicalReads": 99,
    "ResourcePoolID": 257,
    "TaskTime": 43216568,
    "CurrentVersion": "2.17.41",
    "RowCount": 8,
    "IncrementalId": 18894454167,
    "ClientHostname": "SPRC-015",
    "ServerInstanceName": "INSTANCE1",
    "ExeDate": "2022-06-16T23:00:00+10",
    "OwnerId": 3,
    "ServerPrincipalName": "Admin"
  }

Leave a Comment