setQuerySettings

On this page

Definition

Syntax
Command Fields
Examples
Learn More

Definition

setQuerySettings

New in version 8.0.

You can use query settings to add index hints, define operation rejection filters, and set other fields. The settings apply to the query shape on the entire cluster. The cluster retains the settings after shutdown.

For example, query settings allow you to use an index for all executions of a query shape in a cluster.

The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query.

setQuerySettings defines query settings used by the find, distinct, and aggregate commands.

Starting in MongoDB 8.0, use query settings instead of index filters. Index filters are deprecated starting in MongoDB 8.0.

Query settings have more functionality than index filters. Also, index filters aren't persistent and you cannot easily create index filters for all cluster nodes.

Note

To remove query settings, use removeQuerySettings. To obtain the query settings, use a $querySettings stage in an aggregation pipeline.

Syntax

You can add or update query settings using either of the two syntax specifications shown in this section.

Set Query Settings by Passing in a Query

In the following syntax, you provide:

The same fields as a find, distinct, or aggregate command. See the syntax sections on the pages for those commands for the fields you can include in setQuerySettings.
A $db field to specify the database for the query settings.
A settings document with indexHints and other fields.

db.adminCommand( {
   setQuerySettings: {
      <fields>,  // Provide fields for
                 // find, distinct, or aggregate command
      $db: <string>  // Provide a database name
   },
   // Provide a settings document with indexHints and other fields
   settings: {
      indexHints: [ {
         ns: { db: <string>, coll: <string> },
         allowedIndexes: <array>
      }, ... ],
      queryFramework: <string>,
      reject: <boolean>
   }
} )

Set Query Settings by Passing in a Query Shape Hash

You can provide an existing query shape hash string in setQuerySettings and an updated settings document with indexHints and other fields:

db.adminCommand( {
   setQuerySettings: <string>,  // Provide an existing query shape hash string
   // Provide a settings document with indexHints and other fields
   settings: {
      indexHints: [ {
         ns: { db: <string>, coll: <string> },
         allowedIndexes: <array>
      }, ... ],
      queryFramework: <string>,
      reject: <boolean>
   }
} )

A query shape hash is a string that uniquely identifies the query shape. An example query shape hash is "F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C".

To obtain the query shape hash string, do any of these:

Use a $querySettings stage in an aggregation pipeline and examine the queryShapeHash field.
Examine the database profiler output.
View the slow query logs.

If you set the query settings using a hash string, then you won't have the representativeQuery field in the $querySettings aggregation stage output.

Tip

In both syntax variations, you can provide an array of indexHints documents. You can omit the array brackets if you provide only one indexHints document.

Command Fields

The command takes these fields:

Field

Field Type (document, string, ...)

Necessity

Description

setQuerySettings

document or string‎

Required

You can provide either:

The same fields as those in a find, distinct, or aggregate command, and a $db field with the database associated with the original command.
An existing query shape hash string that uniquely identifies the query shape. An example query shape hash is "F42757F1AEB68B4C5A6DE6182B29B01947C829C926BCC01226BDA4DDE799766C"`.

indexHints.ns

document

Optional

Namespace for index hints. Only required when optional index hints are specified.

`db`	string	Required	Name of the database for index hints.
`coll`	string	Required	Name of the collection for index hints.

indexHints.allowedIndexes

array

Optional

Array of indexes for index hints. An index hint can be one of these:

Index name
index key pattern
$natural hint

For more details, see Indexes and hint().

queryFramework

string

Optional

Query framework string can be set to:

classic to use the classic engine.
sbe to use slot-based query execution engine. For details, see Slot-Based Query Execution Engine.

reject

boolean

Optional

If true:

New queries with the matching query shape are rejected and the query response states the query is rejected.
Any queries currently in progress aren't rejected.

Default is false.

To enable a query shape, run setQuerySettings again for the query shape and set reject to false. If you set reject to true and then back to false using setQuerySettings then:

If your settings document isn't empty, then setQuerySettings enables the query shape.
If your settings document only contains reject: false, then setQuerySettings returns an error. Instead, use the removeQuerySettings command to remove the settings and then use setQuerySettings to add query settings.

Examples

The following examples create a collection and add query settings for different commands. The examples use one index for all executions of a query shape run in the cluster.

Create the example collection and indexes

Run:

 // Create pizzaOrders collection
 db.pizzaOrders.insertMany( [
   { _id: 0, type: "pepperoni", size: "small", price: 19,
     totalNumber: 10, orderDate: ISODate( "2023-03-13T08:14:30Z" ) },
   { _id: 1, type: "pepperoni", size: "medium", price: 20,
     totalNumber: 20, orderDate: ISODate( "2023-03-13T09:13:24Z" ) },
   { _id: 2, type: "pepperoni", size: "large", price: 21,
     totalNumber: 30, orderDate: ISODate( "2023-03-17T09:22:12Z" ) },
   { _id: 3, type: "cheese", size: "small", price: 12,
     totalNumber: 15, orderDate: ISODate( "2023-03-13T11:21:39.736Z" ) },
   { _id: 4, type: "cheese", size: "medium", price: 13,
     totalNumber: 50, orderDate: ISODate( "2024-01-12T21:23:13.331Z" ) },
   { _id: 5, type: "cheese", size: "large", price: 14,
     totalNumber: 10, orderDate: ISODate( "2024-01-12T05:08:13Z" ) },
   { _id: 6, type: "vegan", size: "small", price: 17,
     totalNumber: 10, orderDate: ISODate( "2023-01-13T05:08:13Z" ) },
   { _id: 7, type: "vegan", size: "medium", price: 18,
     totalNumber: 10, orderDate: ISODate( "2023-01-13T05:10:13Z" ) }
] )
 // Create ascending index on orderDate field
 db.pizzaOrders.createIndex( { orderDate: 1 } )
 // Create ascending index on totalNumber field
 db.pizzaOrders.createIndex( { totalNumber: 1 } )

The indexes have the default names orderDate_1 and totalNumber_1.

Add query settings for a find command

The following example adds query settings for a find command. The example provides fields in setQuerySettings for the find command, and includes the orderDate_1 index in allowedIndexes.

db.adminCommand( {
   setQuerySettings: {
      find: "pizzaOrders",
      filter: {
         orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) }
      },
      sort: {
         totalNumber: 1
      },
      $db: "test"
   },
   settings: {
      indexHints: {
         ns: { db: "test", coll: "pizzaOrders" },
         allowedIndexes: [ "orderDate_1" ]
      },
      queryFramework: "classic"
   }
} )

(Optional) Verify the query settings

Run this explain command:

db.pizzaOrders.explain().find( { orderDate: { $gt: ISODate(
"2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 } )

The following truncated output shows the query settings are set:

queryPlanner: {
   winningPlan: {
     stage: 'SINGLE_SHARD',
     shards: [
       {
         explainVersion: '1',
         ...
         namespace: 'test.pizzaOrders',
         indexFilterSet: false,
         parsedQuery: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
         querySettings: {
           indexHints: {
             ns: { db: 'test', coll: 'pizzaOrders' },
             allowedIndexes: [ 'orderDate_1' ]
           },
           queryFramework: 'classic'
         },
         ...
       }
     ...
     ]
   }
}

(Optional) Run the query

The following example runs the query:

db.pizzaOrders.find(
   { orderDate: { $gt: ISODate( "2023-01-20T00:00:00Z" ) } } ).sort( { totalNumber: 1 }
)

The query optimizer uses the query settings as an additional input during query planning, which affects the plan selected to run the query.

Query output:

[
   {
      _id: 0,
      type: 'pepperoni',
      size: 'small',
      price: 19,
      totalNumber: 10,
      orderDate: ISODate('2023-03-13T08:14:30.000Z')
   },
   {
      _id: 5,
      type: 'cheese',
      size: 'large',
      price: 14,
      totalNumber: 10,
      orderDate: ISODate('2024-01-12T05:08:13.000Z')
   },
   {
      _id: 3,
      type: 'cheese',
      size: 'small',
      price: 12,
      totalNumber: 15,
      orderDate: ISODate('2023-03-13T11:21:39.736Z')
   },
   {
      _id: 1,
      type: 'pepperoni',
      size: 'medium',
      price: 20,
      totalNumber: 20,
      orderDate: ISODate('2023-03-13T09:13:24.000Z')
   },
   {
      _id: 2,
      type: 'pepperoni',
      size: 'large',
      price: 21,
      totalNumber: 30,
      orderDate: ISODate('2023-03-17T09:22:12.000Z')
   },
   {
      _id: 4,
      type: 'cheese',
      size: 'medium',
      price: 13,
      totalNumber: 50,
      orderDate: ISODate('2024-01-12T21:23:13.331Z')
   }
]

(Optional) Obtain the query settings

The following example uses a $querySettings stage in an aggregation pipeline to obtain the query settings:

db.aggregate( [
   { $querySettings: {} }
] )

Truncated output, which includes the queryShapeHash field:

[
   {
      queryShapeHash: 'AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1',
      settings: {
         indexHints: [
            {
               ns: { db: 'test', coll: 'pizzaOrders' },
               allowedIndexes: [ 'orderDate_1' ]
            }
         ],
         queryFramework: 'classic'
      },
      representativeQuery: {
         find: 'pizzaOrders',
         filter: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
         sort: { totalNumber: 1 },
         '$db': 'test'
      }
   }
]

Add query settings for a distinct command

The following example adds query settings for a distinct command:

db.adminCommand( {
   setQuerySettings: {
      distinct: "pizzaOrders",
      key: "totalNumber",
      query: { type: "pepperoni"} ,
      $db: "test"
   },
   settings: {
      indexHints: {
         ns: { db: "test", coll: "pizzaOrders" },
         allowedIndexes: [ "orderDate_1" ]
      },
      queryFramework: "classic"
   }
} )

Add query settings for an aggregate command

The following example adds query settings for an aggregate command:

db.adminCommand( {
   setQuerySettings: {
      aggregate: "pizzaOrders",
      pipeline: [
         { $match: { size: "medium" } },
         { $group: {
            _id: "$type",
            totalMediumPizzaOrdersGroupedByType: { $sum: "$totalNumber" }
         } }
      ],
      $db: "test"
   },
   settings: {
      indexHints: {
         ns: { db: "test", coll: "pizzaOrders" },
         allowedIndexes: [ "totalNumber_1" ]
      },
      queryFramework: "classic"
   }
} )

Learn More

Back

setDefaultRWConcern

setUserWriteBlockMode