Subqueries

A subquery is a complete Brinqa Query Language (BQL) statement nested inside another query. Use a subquery when the values you want to filter on are themselves the result of a query, so that you can answer the question in a single request instead of running one query, collecting its results, and pasting them into a second query.

Subqueries are supported with the IN and NOT IN operators in a WHERE clause. Use IN to keep only the records that match the subquery's results, and NOT IN to exclude them.

Availability

Filtering with IN or NOT IN against a subquery, including the Top-N pattern described below, is supported starting in Brinqa Platform 12.1.403.

Syntax

Place the subquery in parentheses on the right side of the IN operator:

FIND <DataModel> AS <alias>
WHERE <alias> IN (
  <subquery>
)
RETURN <alias>

The subquery is a full BQL statement. It can traverse relationships with THAT, aggregate, sort with ORDER BY, and cap its own results with LIMIT. It must include a RETURN clause naming the entity that the outer query compares against; if the subquery uses ORDER BY or LIMIT, place them after the subquery's RETURN clause.

When you write a subquery, keep the following in mind:

• Give the subquery its own aliases. Subqueries are non-correlated: the subquery is resolved once, on its own, before the outer query filters against its results. It cannot reference an alias from the outer query, so every alias it uses must be declared inside the subquery.
• The RETURN clause of the subquery must name a single entity, not a list of attributes. In the example below, the subquery returns r2, which is the RiskOwner alias that the outer WHERE r IN (...) filter compares against.
• Apply ORDER BY and LIMIT inside the subquery when you want to narrow the set of values the outer query filters on. The subquery's own LIMIT is always applied to the subquery's results. The outer query's ORDER BY and LIMIT apply only to the final result set.

Example: report on the top 10 risk owners

A common use for a subquery is a Top-N report: first rank a set of records, then report in detail on only the top entries. Without a subquery, this takes two queries. With a subquery, it takes one.

The following query finds the 10 risk owners with the most open source findings, then returns a risk rating breakdown for just those owners:

FIND OpenSourceFinding AS o THAT OWNS_RISK >> RiskOwner AS r
WHERE r IN (
  FIND OpenSourceFinding AS o2 THAT OWNS_RISK >> RiskOwner AS r2
  RETURN r2
  ORDER BY count(o2.id) DESC
  LIMIT 10
)
RETURN
  r.name AS riskOwner,
  o.riskRating AS riskRating,
  count(o.id) AS findingCount
ORDER BY riskOwner, findingCount DESC

Reading the query from the inside out:

1. The subquery traverses from OpenSourceFinding to RiskOwner, counts the findings owned by each risk owner with count(o2.id), sorts owners from most findings to fewest, and keeps the top 10.
2. The outer query performs the same traversal, but WHERE r IN (...) restricts it to the 10 owners the subquery returned.
3. The RETURN clause groups the surviving findings by owner and risk rating, producing a count per combination.

The result is one row per owner and risk rating, for example:

riskOwner	riskRating	findingCount
Cloud Platform Team	Critical	93
Cloud Platform Team	High	422
Cloud Platform Team	Medium	454
Cloud Platform Team	Low	933

Filter on the entity alias when ranking by an aggregate

When the subquery ranks its results with an aggregate ORDER BY, as the Top-N example does with ORDER BY count(o2.id) DESC, filter on the entity alias:

FIND OpenSourceFinding AS o THAT OWNS_RISK >> RiskOwner AS r
WHERE r IN ( ... )

Filtering on an attribute that references the same entity is not reliable in this case, and the query may return an error instead of results:

FIND OpenSourceFinding AS o
WHERE o.riskOwner IN ( ... )

Both forms work when the subquery does not rank by an aggregate. If you need the Top-N pattern and cannot restructure the outer query to traverse the relationship, use the two-query approach described below.

Alternative: split the report into two queries

You do not have to use a subquery for a Top-N report. If you prefer to keep each query simple, or you want to inspect the ranked list before running the detail report, you can run the two steps separately.

First, retrieve the top 10 owner IDs:

FIND OpenSourceFinding AS o2 THAT OWNS_RISK >> RiskOwner AS r2
RETURN r2.id AS ownerId
ORDER BY count(o2.id) DESC
LIMIT 10

Then, filter the detail report against that literal list of IDs:

FIND OpenSourceFinding AS o
WHERE o.riskOwner IN [id1, id2, ..., id10]
RETURN o.riskOwner AS riskOwner,
       o.riskRating AS riskRating,
       count(o.id) AS findingCount
ORDER BY riskOwner, findingCount DESC

This approach requires two round trips but returns the same results, and it works on Brinqa Platform versions earlier than 12.1.403.

Related articles

• List operators
• Sorting and pagination
• Complex queries