# Can I improve this aggregation query?

**URL:** <https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843>\
**Category:** Elasticsearch\
**Created:** [January 19, 2022, 3:45pm UTC](https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843 "2022-01-19T15:45:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![elasticshane](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticshane/32/99458_2.png) [@elasticshane](https://discuss.elastic.co/u/elasticshane)\
**Post date:** [January 19, 2022, 3:45pm UTC](https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843/1 "2022-01-19T15:45:34Z")

</div>

Hello,

I'm quite new to Elasticsearch, but have made some progress with a query, and I'd really appreciate your feedback/ideas about how I've approached my requirement.

**Introduction/Mappings**

I'm writing a query to determine room availability for a number of guests over a date period. My mappings look like this:

```auto
"properties": {
    "basic_type": {
        "type": "keyword"
    },
    "room_id": {
        "type": "keyword"
    },
    "active": {
        "type": "boolean"
    },
    "is_private": {
        "type": "boolean"
    },
    "date": {
        "type": "date"
    },
    "capacity": {
        "type": "integer"
    },
    "property_id": {
        "type": "integer"
    }
}

```

**Query**

My 'steps' for the query are somewhat complicated:

1. For each room (identified by `room_id`), get the minimum capacity over the date range.
2. Then, sum these minimum capacities and bucket them based on the `is_private` field.
3. [Not implemented] Get the largest of these 'bucketed sums'.

Here's what I have, with a filter based on `property_id` and `date range` (note that the room can be inactive, so I'm also filtering on the `active` field too):

```auto
GET rooms_test/_search?size=0
{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "property_id": [
              "1",
              "2"
            ]
          }
        },
        {
          "term": {
            "active": {
              "value": true
            }
          }
        },
        {
          "range": {
            "date": {
              "gte": "2022-06-01",
              "lte": "2022-06-02"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "agg_by_property": {
      "terms": {
        "field": "property_id"
      },
      "aggs": {
        "agg_by_room_type": {
          "terms": {
            "field": "is_private"
          },
          "aggs": {
            "agg_capacity_by_room_type": {
              "sum_bucket": {
                "buckets_path": "agg_by_room>agg_min_space_over_period"
              }
            },
            "agg_by_room": {
              "terms": {
                "field": "room_id"
              },
              "aggs": {
                "agg_min_space_over_period": {
                  "min": {
                    "field": "capacity"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

**Results**

For brevity, I've just pasted the first bucket, relating to property ID: 1:

```auto
{
  "key": 1,
  "doc_count": 8,
  "agg_by_room_type": {
    "doc_count_error_upper_bound": 0,
    "sum_other_doc_count": 0,
    "buckets": [
      {
        "key": 0,
        "key_as_string": "false",
        "doc_count": 4,
        "agg_by_room": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": "MD_1",
              "doc_count": 2,
              "agg_min_space_over_period": {
                "value": 5.0
              }
            },
            {
              "key": "MD_2",
              "doc_count": 2,
              "agg_min_space_over_period": {
                "value": 10.0
              }
            }
          ]
        },
        "agg_capacity_by_room_type": {
          "value": 15.0
        }
      },
      {
        "key": 1,
        "key_as_string": "true",
        "doc_count": 4,
        "agg_by_room": {
          "doc_count_error_upper_bound": 0,
          "sum_other_doc_count": 0,
          "buckets": [
            {
              "key": "DR_1",
              "doc_count": 2,
              "agg_min_space_over_period": {
                "value": 2.0
              }
            },
            {
              "key": "SR_1",
              "doc_count": 2,
              "agg_min_space_over_period": {
                "value": 3.0
              }
            }
          ]
        },
        "agg_capacity_by_room_type": {
          "value": 5.0
        }
      }
    ]
  }
}

```

**Can I improve what I have?**

1. I have those `agg_capacity_by_room_type`, but ideally need to get the largest of the two per property (`isPrivate` - `true|false`) and return in the result.
2. Is there a way to reduce the output that comes back? Ultimately, I don't need the `agg_min_space_over_period` to come back for _every room_, and for some properties, this could amount to a lot of data coming over the wire.

Thanks for looking at my post. 🙂

---

<div class="post-metadata">

**Author:** ![elasticshane](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticshane/32/99458_2.png) [@elasticshane](https://discuss.elastic.co/u/elasticshane)\
**Post date:** [January 20, 2022, 11:22am UTC](https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843/2 "2022-01-20T11:22:36Z")

</div>

I figured it out:

1. I added an additional `max_bucket` aggregation (`agg_property_capacity`):

```auto
"aggs": {
  "agg_by_property": {
    "terms": {
      "field": "property_id"
    },
    "aggs": {
      "agg_by_room_type": {
        "terms": {
          "field": "is_private"
        },
        "aggs": {
          "agg_capacity_by_room_type": {
            "sum_bucket": {
              "buckets_path": "agg_by_room>agg_min_space_over_period"
            }
          },
          "agg_by_room": {
            "terms": {
              "field": "room_id"
            },
            "aggs": {
              "agg_min_space_over_period": {
                "min": {
                  "field": "capacity"
                }
              }
            }
          }
        }
      },
      "agg_property_capacity": {
        "max_bucket": {
          "buckets_path": "agg_by_room_type>agg_capacity_by_room_type"
        }
      }
    }
  }
}

```

1. I reduced the amount of data returned by specifying a `filter_path` value:

`filter_path=aggregations.agg_by_property.buckets.key,aggregations.agg_by_property.buckets.agg_property_capacity.value`

this gave me the much smaller response of:

```auto
{
  "aggregations" : {
    "agg_by_property" : {
      "buckets" : [
        {
          "key" : 1,
          "agg_property_capacity" : {
            "value" : 15.0
          }
        },
        {
          "key" : 2,
          "agg_property_capacity" : {
            "value" : 7.0
          }
        }
      ]
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Gustavo\_Llermaly](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gustavo_llermaly/32/92019_2.png) [@Gustavo\_Llermaly](https://discuss.elastic.co/u/Gustavo_Llermaly)\
**Post date:** [January 20, 2022, 2:02pm UTC](https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843/3 "2022-01-20T14:02:53Z")

</div>

> [@elasticshane](#):
>
> Here's what I have, with a filter based on `property_id` and `date range` (note that the room can be inactive, so I'm also filtering on the `active` field too):

Nice progress! . You can use the "size" property to also reduce the amount of buckets returned in case you need only 1.

[Terms aggregation | Elasticsearch Guide [7.16] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-size)

---

<div class="post-metadata">

**Author:** ![elasticshane](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticshane/32/99458_2.png) [@elasticshane](https://discuss.elastic.co/u/elasticshane)\
**Post date:** [January 20, 2022, 3:49pm UTC](https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843/4 "2022-01-20T15:49:34Z")

</div>

Thanks @Gustavo_Llermaly - thanks for the idea, but in this case, I'm looking to return information against the `property_id` terms, so not looking to limit it.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 17, 2022, 3:50pm UTC](https://discuss.elastic.co/t/can-i-improve-this-aggregation-query/294843/5 "2022-02-17T15:50:29Z")

</div>

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