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.
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.
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.
outfieldequivalent, 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 numerical-type fields: Stored in memory
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)
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.