$querySettings (aggregation stage)

Definition

$querySettings

New in version 8.0.

Returns query settings previously added with setQuerySettings. The settings apply to the entire cluster. The cluster retains the settings after shutdown.

Syntax

If you use multiple pipeline stages, put $querySettings first in the pipeline.

db.aggregate( [
 { $querySettings: { showDebugQueryShape: <boolean> } },
 // Add any additional pipeline stages after $querySettings
 ...
] )

Command Fields

$querySettings takes this field:

Field	Type	Necessity	Description
`showDebugQueryShape`	boolean	Optional	If `true`, `$querySettings` returns a debugging version of the query shape output. You'll see an example in the next section. For output details, see Query Shape. Default is `false`.

Examples

The following examples create a collection, add query settings, and return the settings:

Create the example collection and indexes

Run:

// Create pizzaOrders collection
db.pizzaOrders.insertMany( [
 { _id: 0, type: "pepperoni", totalNumber: 5,
 orderDate: new Date( "2024-01-15T12:00:00Z" ) },
 { _id: 1, type: "cheese", totalNumber: 15,
 orderDate: new Date( "2024-01-23T11:12:32Z" ) },
 { _id: 2, type: "vegan", totalNumber: 20,
 orderDate: new Date( "2024-03-20T10:01:12Z" ) }
] )
// 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 the query settings

The following setQuerySettings example adds query settings:

db.adminCommand( {
 setQuerySettings: {
 find: "pizzaOrders",
 filter: {
 orderDate: { $gt: ISODate( "2024-01-20T00:00:00Z" ) }
 },
 sort: {
 totalNumber: 1
 },
 $db: "test"
 },
 settings: {
 indexHints: {
 ns: { db: "test", coll: "pizzaOrders" },
 allowedIndexes: [ "orderDate_1" ]
 },
 queryFramework: "classic",
 comment: "Index hint for orderDate_1 index to improve query performance"
 }
} )

The comment field is available starting in MongoDB 8.1 (and 8.0.4).

Return the query settings

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

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

Because showDebugQueryShape is true, the debugQueryShape document is included in the output. You can use the queryShapeHash identifier to locate the query settings. queryShapeHash and debugQueryShape are highlighted in this output:

[
 {
 queryShapeHash: 'AB8ECADEE8F0EB0F447A30744EB4813AE7E0BFEF523B0870CA10FCBC87F5D8F1',
 settings: {
 indexHints: [
 {
 ns: { db: 'test', coll: 'pizzaOrders' },
 allowedIndexes: [ 'orderDate_1' ]
 }
 ],
 queryFramework: 'classic',
 comment: 'Index hint for orderDate_1 index to improve query performance'
 },
 representativeQuery: {
 find: 'pizzaOrders',
 filter: { orderDate: { '$gt': ISODate('2023-01-20T00:00:00.000Z') } },
 sort: { totalNumber: 1 },
 '$db': 'test'
 },
 debugQueryShape: {
 cmdNs: { db: 'test', coll: 'pizzaOrders' },
 command: 'find',
 filter: { orderDate: { '$gt': '?date' } },
 sort: { totalNumber: 1 }
 }
 }
]

The Node.js examples on this page use the sample_mflix database from the Atlas sample datasets. To learn how to create a free MongoDB Atlas cluster and load the sample datasets, see Get Started in the MongoDB Node.js driver documentation.

To use the MongoDB Node.js driver to add a $querySettings stage to an aggregation pipeline, use the $querySettings operator in a pipeline object.

To use the $querySettings stage, you must first add indexes and querySettings to the collection.

The following code inserts two indexes on the movies collection:

const year = collection.createIndex({ year: 1 }); 
const title = collection.createIndex({ title: 1 });

The following code adds query settings to the movies collection:

const command = {
 setQuerySettings: {
 find: "movies",
 filter: { year: { $gt: 2011 } },
 sort: { title: 1 },
 $db: "sample_mflix"
 },
 settings: {
 indexHints: {
 ns: { db: "sample_mflix", coll: "movies" },
 allowedIndexes: [ "year_1" ]
 },
 queryFramework: "classic",
 comment: "Index hint for year_1 index to improve query performance"
 }
};
const adminDb = client.db("admin");
const result = adminDb.command(command);

The following example creates a pipeline stage that returns the previously added query settings for the movies collection. The example sets the showDebugQueryShape option to true to include the debugQueryShape document in the output. The example then runs the aggregation pipeline:

const pipeline = [ { $querySettings: {showDebugQueryShape: true} } ];
const cursor = adminDb.aggregate(pipeline);
return cursor;

Learn More

Back

$project

$queryStats