How to Create a Histogram of Summed Nested Fields in OpenSearch?

Question

I am working with an OpenSearch index where each document represents a dispute and contains a nested array of transactions. My goal is to compute the sum of a specific field in the nested transactions array for each dispute and then create a histogram of these summed values.

Here is an example of my document structure:

{
  "dispute_id": "123",
  "transactions": [
    {
      "transactionMerchant": "MerchantA",
      "transactionAmount": {
        "amountInCents": 1000,
        "currency": "USD"
      }
    },
    {
      "transactionMerchant": "MerchantB",
      "transactionAmount": {
        "amountInCents": 2000,
        "currency": "USD"
      }
    }
  ]
}

What I’ve Tried

I have successfully created a query that computes the sum of transactions.transactionAmount.amountInCents for each dispute:

POST /dispute/_search
{
  "size": 0,
  "aggs": {
    "by_dispute": {
      "terms": {
        "field": "dispute_id",
        "size": 10
      },
      "aggs": {
        "nested_transactions": {
          "nested": {
            "path": "transactions"
          },
          "aggs": {
            "transaction_sum": {
              "sum": {
                "field": "transactions.transactionAmount.amountInCents"
              }
            }
          }
        }
      }
    }
  }
}

This query returns the sum for each dispute correctly. However, my goal is to create a histogram of these sums. I attempted to use a histogram aggregation but ran into issues with nested aggregation limitations in OpenSearch.

Failed Attempt

Here is an example of the query I tried to create a histogram:

POST /dispute/_search
{
  "size": 0,
  "aggs": {
    "by_dispute": {
      "terms": {
        "field": "dispute_id",
        "size": 10
      },
      "aggs": {
        "nested_transactions": {
          "nested": {
            "path": "transactions"
          },
          "aggs": {
            "transaction_sum": {
              "sum": {
                "field": "transactions.transactionAmount.amountInCents"
              }
            }
          }
        }
      }
    },
    "sum_histogram": {
      "histogram": {
        "field": "by_dispute>nested_transactions>transaction_sum.value",
        "interval": 1000
      }
    }
  }
}

This query returns no histogram following:

"sum_histogram": {
      "buckets": []
},

Question

How can I create a histogram of the summed transaction amounts for each dispute in OpenSearch? Is there a way to achieve this directly in OpenSearch? Any examples or guidance would be greatly appreciated.

Answer 1

    POST /dispute/_search
{
  "size": 0,
  "aggs": {
    "by_dispute": {
      "terms": {
        "field": "dispute_id",
        "size": 10000  // Adjust size as needed
      },
      "aggs": {
        "nested_transactions": {
          "nested": {
            "path": "transactions"
          },
          "aggs": {
            "transaction_sum": {
              "sum": {
                "field": "transactions.transactionAmount.amountInCents"
              }
            }
          }
        },
        "dispute_sum_bucket": {
          "bucket_script": {
            "buckets_path": {
              "sum": "nested_transactions>transaction_sum"
            },
            "script": "params.sum"
          }
        }
      }
    },
    "sum_histogram": {
      "histogram": {
        "field": "by_dispute>dispute_sum_bucket",
        "interval": 1000
      }
    }
  }
}