Nested queries

This is for:

Developer System Administrator
In this article

A nested query is a Coveo query language feature, typically used by developers, allowing to perform a second query on a subset of items obtained by a first query.

Using nested queries, the returned result set is a subset of the initial result set filtered according to fields found outside of the initial query.

Syntax

Each query is delimited by a pair of square brackets:

OutExpression @Outfield=[[@Infield] InExpression]

To be considered nested, a query needs at least two pairs of brackets. The innermost pair of brackets forms a level.

When the outfield and the infield are the same, the following syntax can also be used:

OutExpression [[@Infield] InExpression]

The terms refer to these concepts:

  • OutExpression: The initial query, which returns an initial result set.

  • @Outfield: The field found in your initial result set from which the subset should be built.

  • @Infield: The outfield equivalent, but for the subset.

  • InExpression: The query to be performed to return the subset.

Theoretically, nested queries can support an infinite number of levels. In practice, most nested queries need only one level, sometimes two.

Many level nested queries have the following syntax:

OutExpression @Outfield=[[@Infield] InExpression @Outfield2=[[@Infield2] InExpression2]]
Important

Fields used in nested queries must have the following specific configuration:
Notes

  • Regarding performance, numeric fields will always outperform string fields. This is because:

    • Executing 1 or 1000 numeric field values doesn’t impact performance.

    • Executing 1 string field value versus 1000 string field values is 1000 times longer.

  • Using deeply nested queries will have a significant impact on search response time.

  • For developers with an SQL background, a nested query syntax is similar to the following SQL expression:

      SELECT * FROM Index WHERE Outfield IN (SELECT Infield FROM Index WHERE InExpression)
Examples

  • From an index containing Salesforce CRM information, this query returns all accounts that had sales opportunities over 50,000$ in 2017.

    @objecttype=Account [[@syssfaccountid] @objecttype=opportunity @syssfamount>50000 @year=2017 ]

  • From an index containing music albums information, this recursive query returns artists that have a rock album including at least one song that has the word love in its title.

    @filetype=artist [[@artistid] [[@albumid] @songtitle=(love)] @genre=rock]

  • From an index containing Baseball statistics, this query returns the home runs that were hit by players with at least 50 home runs (hr), more than 100 runs batted in (rbi), and a batting average (avg) greater than 300.

    @type=homerun [[@playerid] @hr>50 @rbi>100 @avg>0.3 @type=batting]

  • On your site, you use the folding feature (see Folding results) to display answers as children of their questions in search results. Your questions are tagged with certain topics, and you have a Topics facet to allow users to filter results. You want to exclude search results tagged with legacy to remove old data. Therefore, you create a custom field (crawlingpage=false) to group all questions tagged legacy and applied the filter NOT crawlingpage=false during query execution.

    However, while legacy questions aren’t shown in the results, their answers are. To obtain all questions and answers not tagged as excluded, you use the following nested query:

    NOT [[@questionid] @crawlingpage=false]

    The first nested query level returns all values for the @questionid nested field, which includes all questions and answers.

    The second level returns only the items (questions and associated answers) for which the question item has the field @crawlingpage=false.

    The NOT excludes these items from the results.

Exact match operator (==) with a nested query

The Search API expands exact field matches with nested queries by first fetching the values of the infield from the index, and then substituting its results into the exact match query.

Example

You perform the following query:

@type==[[@objecttype]]

The Search API fetches the values of the objecttype field from the index and obtains the following values:

["Account", "Contact", "Opportunity"]

The Search API then replaces the bracketed expression with the list of values just returned:

@type==(Account, Contact, Opportunity)

The Search API then executes the exact match query.