Nested Queries
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 constitutes 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: Theoutfieldequivalent, 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, occasionally two.
Multiple 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:
-
For string-type fields: Facet
-
For Coveo Enterprise Search, see Adding a Facet Field
-
For Coveo Cloud V1, see Managing Fields for a Source.
-
For Coveo Cloud V2, see Add/Edit a Field.
-
-
For numerical-type fields: Stored in memory
-
For Coveo Enterprise Search and Coveo Cloud V1, this needs to be done in the index configuration with the assistance of Coveo Support.
-
For Coveo Cloud V2, enable Use cache for nested queries (see Add/Edit a Field).
-
Notes:
-
Using a single pair of brackets will return an Invalid syntax error (see Using Special Characters in Queries).[more]
-
Regarding performance, numerical fields will always outperform string fields. This is because:
-
Executing 1 or 1000 numerical field values does not impact performance.
-
Executing 1 string field value vs 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 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 wish to exclude search results tagged with legacy to remove old data. You thus 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 are not 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.