Same level script aggregation

Elastic Stack Elasticsearch
1

I'm wondering if it is possible to do a same level script aggregation in ES. Let's say I have 2 fields in my documents sales and spend. This is the result I want to achieve:

{
  "size": 0,
  "query": {
    "match_all": {}
  },
  "aggs": {
    "sales": {
      "sum": {
        "field": "sales"
      }
    },
    "spend": {
      "sum": {
        "field": "spend"
      }
    },
    "custom_calulation": {
      // something that will calculate sales/spend
    }
  }
}

I know that I can easily do this using per say a date_histogram and use the bucket_script aggregation, so I'm trying to do same here only with the same level aggregation, so I don't have to nest them.

2

I'm a little unsure about the question. As you know about the bucket_script agg, why didnt it work in your case? Maybe a fully reproducible example would help in this case.

3

So this is the original query:

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "report_date": {
              "gte": "2021-01-01",
              "lte": "2021-12-31"
            }
          }
        },
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "term": {
                  "segment": {
                    "value": "null"
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "segment"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "interval": {
      "date_histogram": {
        "field": "report_date",
        "interval": "year",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2021-01-01",
          "max": "2021-12-31"
        }
      },
      "aggs": {
        "sales": {
          "sum": {
            "field": "attributed_sales_14d"
          }
        },
        "spend": {
          "sum": {
            "field": "cost"
          }
        },
        "clicks": {
          "sum": {
            "field": "clicks"
          }
        },
        "impressions": {
          "sum": {
            "field": "impressions"
          }
        },
        "roas": {
          "bucket_script": {
            "buckets_path": {
              "sales": "sales",
              "cost": "spend"
            },
            "script": "params.sales/params.cost",
            "gap_policy": "insert_zeros"
          }
        }
      }
    },
    "histogram_for_average": {
      "date_histogram": {
        "field": "report_date",
        "interval": "day"
      },
      "aggs": {
        "sales": {
          "sum": {
            "field": "attributed_sales_14d"
          }
        },
        "spend": {
          "sum": {
            "field": "cost"
          }
        },
        "roas": {
          "bucket_script": {
            "buckets_path": {
              "sales": "sales",
              "cost": "spend"
            },
            "script": "params.sales/params.cost",
            "gap_policy": "insert_zeros"
          }
        }
      }
    },
    "average_spend": {
      "avg_bucket": {
        "buckets_path": "histogram_for_average>spend"
      }
    },
    "average_sales": {
      "avg_bucket": {
        "buckets_path": "histogram_for_average>sales"
      }
    },
    "average_roas": {
      "avg_bucket": {
        "buckets_path": "histogram_for_average>roas"
      }
    }
  }
}

This first histogram is used to represent data and can be done with year, day and month values. The average that needs to be calculated must always be done with a day interval. What I want to achieve is to replace the average_roas with a calculation average_sales/average_spend .

To further elaborate on the average_histogram. I have multiple documents for each day and the sales for one day is the sum of all the documents for that individual day. That's why I want to sum them daily and then get the daily average sales. I do the same for spend as well.

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.