Sub aggregation with sum/average with Elasticsearch

avg_bucket pipeline aggregation will do the job in some cases (but might not scale well with the size of the index, see notes below):

POST myindex1/_search
{
  "size": 0,
  "aggs": {
    "by category": {
      "terms": {
        "field": "category.keyword"
      },
      "aggs": {
        "by user_id": {
          "terms": {
            "field": "user_id"
          },
          "aggs": {
            "avg by user": {
              "avg": {
                "field": "amount"
              }
            }
          }
        },
        "average by user, category": {
          "avg_bucket": {
            "buckets_path": "by user_id>avg by user"
          }
        }
      }
    }
  }
}

This will response that looks something like this:

{
  ...
  "aggregations" : {
    "by category" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "insurances",
          "doc_count" : 3,
          "by user_id" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : 2,
                "doc_count" : 2,
                "avg by user" : {
                  "value" : 450.0
                }
              },
              {
                "key" : 1,
                "doc_count" : 1,
                "avg by user" : {
                  "value" : 1000.0
                }
              }
            ]
          },
          "average by user, category" : {
            "value" : 725.0   <--- average for `insurances`
          }
        },
        {
          "key" : "transport",
          "doc_count" : 3,
          "by user_id" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : 1,
                "doc_count" : 2,
                "avg by user" : {
                  "value" : 75.0
                }
              },
              {
                "key" : 2,
                "doc_count" : 1,
                "avg by user" : {
                  "value" : 300.0
                }
              }
            ]
          },
          "average by user, category" : {
            "value" : 187.5      <--- average for `transport`
          }
        }
      ]
    }
  }
}

How does it work?

Let's start with "by user_id" terms aggregation: we ask Elasticsearch to group documents by user_id and to compute average on amount using avg aggregation.

POST myindex1/_search
{
  "size": 0,
  "aggs": {
    "by user_id": {
      "terms": {
        "field": "user_id"
      },
      "aggs": {
        "avg by user": {
          "avg": {
            "field": "amount"
          }
        }
      }
    }
  }
}

This is equivalent to SQL:

SELECT user_id, avg(amount)
FROM my_index
GROUP BY user_id;

So far this is pretty straightforward. But how can we average also on the category now?

We can add another terms aggregation "by category" on top of "by user id" one. Now avg will take into account also the category:

POST myindex1/_search
{
  "size": 0,
  "aggs": {
    "by category": {
      "terms": {
        "field": "category.keyword"
      },
      "aggs": {
        "by user_id": {
          "terms": {
            "field": "user_id"
          },
          "aggs": {
            "avg by user": {
              "avg": {
                "field": "amount"
              }
            }
          }
        }
      }
    }
  }
}

This is equivalent to SQL:

SELECT user_id, category, avg(amount)
FROM my_index
GROUP BY user_id, category;

Can we now use the result from the previous query, and aggregate on category again?

This can be done with avg_bucket pipeline aggregation. The only thing missing is to tell avg_bucket aggregation where exactly to find the buckets to aggregate upon, which is done via a buckets_path expression.

This is how we arrive to the query that I posted in the top, and it will effectively do the equivalent of SQL you posted in the question.

But...

What can go wrong?

The downside of this method is that it does not scale well with the number of documents in the index.

The fact is, a pipeline aggregation only operates with already aggregated data:

Pipeline aggregations work on the outputs produced from other aggregations rather than from document sets, adding information to the output tree.

In our case this means that if there are more than 10 distinct user_id in the index, our averages will not be precise.

This happens because by default terms aggregation only returns top 10 buckets, and the SQL-ish equivalent should be like the following:

SELECT category, avg(avg_amount)
FROM (
    SELECT user_id, category, avg(amount) avg_amount
    FROM my_index
    GROUP BY user_id, category
    LIMIT 10 per user_id
) Q
LIMIT 10;

This limit can be changed via size parameter of terms aggregation.

Another thing to keep in mind is that terms returns approximate document counts, which can affect the averages as well.

Hope that helps!