• Log inStart now

NRQL subqueries

With NRQL, you can run a subquery, which is a query nested inside another query.

Subquery basics

A subquery is a query that is nested inside another query: they allow you to use the result of one query in another query. Examples of things you can do with subqueries:

  • Perform calculations on the child entities of a parent entity
  • See error logs for hosts with high CPU load based on the infrastructure agent's CPU utilization

In NRQL, subqueries can appear in the SELECT statement and WHERE clause.

Example subquery

Here's an example query for getting a count of transactions with an above-average duration:

SELECT count(*) FROM Transaction WHERE duration > (SELECT average(duration) FROM Transaction)

A subquery's results must make sense in context. In the example above, the greater-than condition in the WHERE clause requires a subquery that returns a single value. A subquery that returns a set of values (e.g. a uniques() subquery) would fail.

A subquery may be nested inside another subquery. A maximum of three subqueries, nested or unnested, is allowed in a query.

The subquery's time range will be the same as the outer query's, unless explicitly specified with SINCE/UNTIL.

Subquery execution

During query execution, each subquery is run independently, and its result is used as a constant value, or set of values, in the outer query. Due to this execution model, subqueries may not reference attributes and values from the outer query.

The query duration limit is honored for queries with subqueries. This means that all subqueries and the outer query must complete execution within the duration limit.

The maximum number of results a subquery can return is the same as the LIMIT MAX value: 2,000. When that limit is exceeded, it may result in incomplete results and this error message: "Subquery may have reached the maximum result member limit, which may cause an incomplete result."

Subqueries vs. nested aggregation

Though they appear similar, subqueries shouldn't be confused with nested aggregations, which allow the aggregation of the result of a nested query. Nested aggregations are used in the FROM clause, while subqueries are used in the SELECT statement and WHERE clause.

Limitations

  • Subqueries aren't supported in NRQL alert conditions. Because queries with subqueries require making multiple passes over data, subqueries are incompatible with streaming alerts.
  • The TIMESERIES and COMPARE WITH clauses, which cause queries to return multiple sets of results, are not supported in subqueries.
  • Subqueries can't appear in the FACET clause, though they can appear in the WHERE clauses used in FACET CASES.
  • Has limits on the number of results

Example subqueries

Here are some example subqueries:

Copyright © 2022 New Relic Inc.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.