elasticsearch min and max aggs on other aggregation results

Question

I am trying to find min and max of an aggregation result in elasticsearch. what query will allow it to first aggregate by a field and then perform monthy sub aggregation to get monthly data for that field and then find min and max among those monthly calculated metric. i am stuck at finding min and max. for rest of the requirement below is my query

The question can be phrased as : what is the max and min transaction (considering all months) in any country ? here is the query that i tried:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "Id": "7466swy7893jgs225"
          }
        }
      ]
    }
  },
  "aggs": {
    "dimension": {
      "terms": {
        "field": "country"
      },
      "aggs": {
        "MoM": {
          "date_histogram": {
            "field": "date",
            "interval": "month",
            "format": "MMM YY"
          },
          "aggs": {
            "totalValue": {
              "sum": {
                "field": "Transactions"
              }
            }
          }
        }
      }
    }
  },
  "size": 0

}

This is the output that i get:

   {
    "aggregations": {
    "dimension": {
    "doc_count_error_upper_bound": 0,
    "sum_other_doc_count": 0,
    "buckets": [
    {
        "key": "USA",
        "doc_count": 392,
        "MoM": {
            "buckets": [
            {
            "key_as_string": "jan 16",
            "key": 1462060800000,
            "doc_count": 31,
            "totalValue": { "value": 352 }
            },
            {
            "key_as_string": "Feb 16",
            "key": 1462060800000,
            "doc_count": 31,
            "totalValue": { "value": 429 }
            }
         ??????????????/MIN and MAX of 352 AND 429
          ]
        }
    },
    {
        "key": "Bhutan",
        "doc_count": 392,
        "MoM": {
            "buckets": [
            {
            "key_as_string": "Jan 16",
            "key": 1462060800000,
            "doc_count": 31,
            "totalValue": {"value": 750 }
            }
            ,
            {
            "key_as_string": "Feb 16",
            "key": 1464739200000,
            "doc_count": 30,
            "totalValue": {"value": 827 }
        }
          ??????????????/MIN and MAX of 750 AND 827

      ]
     }
  }
 ]
 }
 }}

Answer 1

You need to use Pipeline Aggregation.. Try Max Bucket and Min Bucket aggregation

{
"size": 0,
"aggs": {
  "dimension": {
     "terms": {
        "field": "country"
     },
     "aggs": {
        "MoM": {
           "date_histogram": {
              "field": "date",
              "interval": "month",
              "format": "MMM YY"
           },
           "aggs": {
              "totalValue": {
                 "sum": {
                    "field": "transactions"
                 }
              }
           }
        },
        "max_monthly_temp": {
           "max_bucket": {
              "buckets_path": "MoM>totalValue"
           }
        },
        "min_monthly_temp": {
           "min_bucket": {
              "buckets_path": "MoM>totalValue"
              }
            }
         }
      }
   }
 }

Output will be somewhat like :

 "buckets": [
        {
           "key": "India",
           "doc_count": 5,
           "MoM": {
              "buckets": [
                 {
                    "key_as_string": "Feb 17",
                    "key": 1485907200000,
                    "doc_count": 3,
                    "totalValue": {
                       "value": 260
                    }
                 },
                 {
                    "key_as_string": "Mar 17",
                    "key": 1488326400000,
                    "doc_count": 1,
                    "totalValue": {
                       "value": 115
                    }
                 },
                 {
                    "key_as_string": "Apr 17",
                    "key": 1491004800000,
                    "doc_count": 1,
                    "totalValue": {
                       "value": 5
                    }
                 }
              ]
           },
           "max_monthly_temp": {
              "value": 260,
              "keys": [
                 "Feb 17"
              ]
           },
           "min_monthly_temp": {
              "value": 5,
              "keys": [
                 "Apr 17"
              ]
           }
        },
    ......
      ]

Hope this helps..