--- title: Nested Queries description: Datadog, the leading service for cloud-scale monitoring. breadcrumbs: Docs > Metrics > Nested Queries --- # Nested Queries ## Overview{% #overview %} By default, every metric query in Datadog consists of two layers of aggregation. Nested queries allows you to reuse the results of a previous query in a subsequent one. {% video url="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-example-video.mp4" /%} Nested queries unlocks several powerful capabilities: - [Multilayer aggregation](https://docs.datadoghq.com/metrics/nested_queries/#multilayer-aggregation) - [Percentiles and standard deviation on count/rate/gauge type metrics](https://docs.datadoghq.com/metrics/nested_queries/#percentiles-and-standard-deviation-for-aggregated-counts-rates-and-gauges) - [Higher resolution queries over historical timeframes](https://docs.datadoghq.com/metrics/nested_queries/#higher-resolution-queries-over-historical-time-frames) ## Multilayer aggregation{% #multilayer-aggregation %} In Datadog, each metric query in Datadog is evaluated with two layers of aggregation: first by time, then by space. Multilayer aggregation allows you to apply additional layers of time or space aggregation. For more information on aggregation, see the [anatomy of a metric's query](https://docs.datadoghq.com/metrics/#anatomy-of-a-metric-query). {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-before-after.f75d3108b44d29a817910f56e3af9df9.png?auto=format" alt="example of applying nested queries before and after" /%} ### Multilayer time aggregation{% #multilayer-time-aggregation %} Access multilayer time aggregation with the `rollup` function. Every metric query already contains an initial `rollup` (time aggregation) that controls the granularity of the data points displayed on the graph. For more information, see the [Rollup](https://docs.datadoghq.com/dashboards/functions/rollup/) documentation. You can apply additional layers of time aggregation with subsequent rollups. The first rollup supports the following aggregators: - `avg` - `sum` - `min` - `max` - `count` Additional layers provided by multilayer time aggregation supports the following time aggregators: - `avg` - `sum` - `min` - `max` - `count` - `arbitrary percentile pxx` (`p78, p99, p99.99, etc.`) - `stddev` Multilayer time aggregation can be used with the following functions: | Supported Functions | Description | | -------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Arithmetic operators | `+, -, *, /` | | Timeshift functions | `{*}, -``hour_before({*})``day_before({*})``week_before({*})``month_before({*})` | | Top-k selection | `top({*}, , '', '')` | Any functions not listed above cannot be combined with multilayer time aggregation. {% collapsible-section %} ##### Time aggregation example query This query first calculates the average CPU utilization for each EC2 instance grouped by `env` and `team`, rolled up into 5-minute intervals. Then multilayer time aggregation is applied to calculate the 95th percentile in time of this nested query over 30m intervals. {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/multilayer-time-agg-ui.ccda79146530fc7cad88df0fae9b88b2.png?auto=format" alt="example of multilayer time aggregation in the JSON" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/multilayer-time-agg-json.ecd1abcadc8c02f3720c105d0f767ffd.png?auto=format" alt="example of multilayer time aggregation in the JSON" /%} {% /collapsible-section %} ### Multilayer space aggregation{% #multilayer-space-aggregation %} After you specify tag(s) in your first layer of space aggregation to group by, access multilayer space aggregation with the `Group By` function. You can apply additional layers of space aggregation with subsequent `Group Bys`. Note: if you do not specify tag(s) to group by in your initial space aggregation layer, multilayer space aggregation will not be available. The first layer of space aggregation supports the following aggregators: - `avg by` - `sum by` - `min by` - `max by` Additional layers of space aggregation support: - `avg by` - `sum by` - `min by` - `max by` - `arbitrary percentile pXX` (`p75, p99, p99.99, etc.`) - `stddev by` Multilayer space aggregation can be used with the following functions: | Supported Functions | Description | | -------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Arithmetic operators | `+, -, *, /` | | Timeshift functions | `{*}, -``hour_before({*})``day_before({*})``week_before({*})``month_before({*})` | | Top-k selection | `top({*}, , '', '')` | Any other functions not listed above cannot be combined with multilayer space aggregation. All space aggregators with the exception of percentile space aggregators have one argument, which is the tag key(s) you want to group by. Percentile space aggregators require two arguments: - The arbitrary percentile pXX - The tag(s) to group by {% collapsible-section %} ##### Space aggregation example queries This initial query, `avg:aws.ec2.cpuutilization{*} by {env,host}.rollup(avg, 300)` calculates the sum of average CPU utilization, grouped by `env` and `host` every 5 minutes. Then multilayer space aggregation is applied to calculate maximum value of the average CPU utilization by `env`. In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/multilayer-space-agg-ui.d27fa7007fafdc34928a2d274c9d934a.png?auto=format" alt="example of multilayer space aggregation in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/multilayer-space-agg-json.65244146fd7682283da6dad7078f857b.png?auto=format" alt="example of multilayer space aggregation in the JSON" /%} {% /collapsible-section %} ## Percentiles and Standard Deviation for aggregated counts, rates, and gauges{% #percentiles-and-standard-deviation-for-aggregated-counts-rates-and-gauges %} You can use multilayer aggregation (time and space) to query percentiles and standard deviation from queries on counts, rates, and gauges. They allow you to better understand the variability and spread of your large datasets and allow you to better identify outliers. **Note**: The percentile or standard deviation aggregators in Nested queries are calculated using the results of an existing, aggregated count, rate, or gauge metrics. For globally accurate percentiles that are computed on unaggregated, raw values of a metric, use [distribution metrics](https://docs.datadoghq.com/metrics/distributions/) instead. {% collapsible-section %} ##### Percentiles in Multilayer Time Aggregation example query We can use percentiles in multilayer time aggregation to summarize the results of our nested query (avg CPU utilization by `env` and `team` every 5 minutes) by calculating the p95th value of this nested query every 30 minutes. {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/percentiles-time-agg-ui.21000ded0ec1f827df0f5dcbb074032d.png?auto=format" alt="example of MLA time agg percentiles using nested queries in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/percentiles-time-agg-json.d05b1b55ea965c0a923a892147c74ac5.png?auto=format" alt="example of MLA time agg percentiles using nested queries in the JSON" /%} {% /collapsible-section %} {% collapsible-section %} ##### Percentiles in Multilayer Space Aggregation example query We can use percentiles in multilayer space aggregation to summarize the results of our nested query (avg CPU utilization by `env` and `team` every 5 minutes) by calculating the p95th value of this nested query for every unique `env` value. In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/percentiles-space-agg-ui.a370850b957f7c80d609747fd28f807b.png?auto=format" alt="example of MLA space agg percentiles using nested queries in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/percentiles-space-agg-json.a43292e3d404ee090bd8109299259e2d.png?auto=format" alt="example of MLA space agg percentiles using nested queries in the JSON" /%} {% /collapsible-section %} {% collapsible-section %} ##### Standard deviation example query Standard deviation helps measure the variability or dispersion of a dataset. The following query uses standard deviation with multilayer time aggregation to calculate the standard deviation of our nested query (sum of API request counts, averaged over 4 hour) over longer twelve-hour periods: In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-std-ui.f7fc86cd0557b82268c8deca561322bd.png?auto=format" alt="example of standard deviation with nested queries in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-std-json.3a9058fe3b05184ee426cff03bb11055.png?auto=format" alt="example of standard deviation with nested queries in the JSON" /%} {% /collapsible-section %} ## Higher resolution queries over historical time frames{% #higher-resolution-queries-over-historical-time-frames %} Every metric query contains an initial layer of time aggregation (rollup) which controls the granularity of datapoints shown. Datadog provides default rollup time intervals that increase as your overall query timeframe grows. With nested queries, you can access more granular, high-resolution data over longer, historical timeframes. {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/higher-res-query-example.9e566e5fd3d1599b2de5cdf38c5333d2.png?auto=format" alt="example of higher resolution queries over historical timeframes in the UI" /%} {% collapsible-section %} ##### Higher resolution example query Historically, when querying a metric over the past month, you would see data at 4-hour granularity by default. You can use nested queries to access higher granularity data over this historical timeframe. Here's an example query graphed over the past month where the query batch count is initially rolled up in 5 minute intervals. Then multilayer time aggregation is applied to calculate the standard deviation in time of this nested query over 4 hour intervals for a more human-readable graph. ***Note**: Datadog recommends that you define your initial rollup with the most granular rollup interval and use multilayer time aggregation with coarser rollup intervals to get more user-readable graphs.* In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-higher-res-ui.e591fcc096c04e2d7d44e1f3336ac6c1.png?auto=format" alt="example of higher resolution queries using nested queries in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-higher-res-json.a08aec553f080ee2c9dfdf16802be68a.png?auto=format" alt="example of higher resolution queries using nested queries in the JSON" /%} {% /collapsible-section %} ## Moving rollup{% #moving-rollup %} Datadog provides a `moving_rollup` function that enables aggregation of datapoints over a specified time window. See [moving-rollup](https://docs.datadoghq.com/dashboards/functions/rollup/#moving-rollup) for more information. By using nested queries, you can extend its functionality to incorporate lookback mode, allowing you to analyze datapoints beyond the original query window. This provides a more comprehensive view of your query's trends and patterns over the specified time window. {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/moving-rollup-diagram.c62e58e48ec75b9a75851694b35d4459.png?auto=format" alt="example of old vs. new moving_rollup function" /%} The existing version of the `moving_rollup` function only supports the following aggregators: - `avg` - `sum` - `min` - `max` - `median` When nesting queries, only the lookback mode version of the `moving_rollup` function can be used. This version of the function supports the following aggregators: - `avg` - `sum` - `min` - `max` - `count` - `count by` - `arbitrary percentile pxx` (`p78, p99, p99.99, etc.`) - `stddev` {% collapsible-section %} ##### Max Moving rollup with Lookback Mode Enabled When nesting these `moving_rollups`, the rollup intervals provided must get larger as shown in the UI or JSON tab: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/moving_rollup1_ui.4c2ddeb4cb17908a2d0ca9415452b8d6.png?auto=format" alt="example of moving rollup in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/moving_rollup1_json.fd8b745d2e51c7e3d4f35609374b5584.png?auto=format" alt="example of moving rollup in the JSON" /%} {% /collapsible-section %} {% collapsible-section %} ##### Standard Deviation Moving Rollup with Lookback Mode Enabled You can also use percentiles and standard deviation with the new moving rollup function, which supports lookback, and allows nesting of moving rollups with lookback enabled. In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/moving_rollup2_ui.9df79619917061a48fd2800ff6cae307.png?auto=format" alt="example of moving rollup with standard deviation in the UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/moving_rollup2_json.3cbe6765f5ed3e0a17bf2583c562b583.png?auto=format" alt="example of moving rollup with standard deviation in the JSON" /%} {% /collapsible-section %} ## Boolean threshold remapping functions{% #boolean-threshold-remapping-functions %} Remap functions allow you to refine and transform query results based on specific conditions, extending functionality for monitoring and analysis. Nested queries unlocks the following three new functions: - `is_greater` (`, `) - `is_less` (`, `) - `is_between` (`, , `) {% collapsible-section %} ##### is_greater() example query `is_greater()` returns 1.0 for each point where the query is greater than a constant of 30 and 0.0 elsewhere. In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/is_greater_ui.9fa82b167e869c7666f35a6d2c41277a.png?auto=format" alt="example of is_greater mapping function in UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/is_greater_json.ab45357f322ee3f7a5c52ef011fd4aa0.png?auto=format" alt="example of is_greater mapping function in JSON" /%} {% /collapsible-section %} {% collapsible-section %} ##### is_less() example query `is_less()` returns 1.0 for each point where the query is less than a constant of 30 and 0.0 elsewhere. In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/is_less_ui.73963ff67ed13b6197d381275809952e.png?auto=format" alt="example of is_less mapping function in UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/is_less_json.f99639ae5d0723b22cc5f203cc864943.png?auto=format" alt="example of is_less mapping function in JSON" /%} {% /collapsible-section %} {% collapsible-section %} ##### is_between() example query `is_between()` returns 1.0 for each point where the query is between 10 and 30 (exclusive), and 0.0 elsewhere. In the UI or JSON tab, it would look as follows: {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/is_between_ui.ce15fe7827ed64eeffa77e1e794c1973.png?auto=format" alt="example of is_between mapping function in UI" /%} {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/is_between_json.5da5d43a8491a5787fd4898a680a907d.png?auto=format" alt="example of is_between mapping function in JSON" /%} {% /collapsible-section %} ## Use nested queries with Datadog's API{% #use-nested-queries-with-datadogs-api %} You can use nested queries functionality in our [public API for querying timeseries data](https://docs.datadoghq.com/metrics/#query-timeseries-data-across-multiple-products). Change the contents of the **formula** object {% image source="https://datadog-docs.imgix.net/images/metrics/nested_queries/nested-queries-using-api.7e194e8cc04271aab03df68db14034f4.png?auto=format" alt="example of higher resolution queries using nested queries in the JSON" /%} ## Further reading{% #further-reading %} - [Dashboard Querying](https://docs.datadoghq.com/dashboards/querying/) - [Discover powerful insights with nested metric queries](https://www.datadoghq.com/blog/nested-queries/)