Docs Menu

Docs HomeDevelop ApplicationsMongoDB Manual

$queryStats (aggregation)

On this page

  • Definition
  • Requirements
  • Syntax
  • Command Fields
  • Access Control
  • Behavior
  • How $queryStats Tracks Query Statistics
  • How $queryStats Groups Returned Documents
  • How $queryStats Transforms Data Using transformIdentifiers
  • Output
  • Collection Type
  • Query Shape
  • Examples
  • Untransformed Example
  • Transformed Example
  • MongoDB Atlas Data Collection
$queryStats

Warning

The $queryStats aggregation stage is unsupported and is not guaranteed to be stable in a future release. Don't build functionality that relies on a specific output format of this stage, since the output may change in a future release.

Returns runtime statistics for recorded queries.

$queryStats collects and reports metrics for aggregate() and find() queries. $queryStats does not collect information for queries that use Queryable Encryption.

The $queryStats stage is enabled on deployments hosted on MongoDB Atlas with a cluster tier of at least M10.

To run the $queryStats stage, your pipeline must meet the following requirements:

  • The pipeline must be run on the admin database.

  • $queryStats must be the first stage in the pipeline.

db.adminCommand( {
aggregate: 1,
pipeline: [
{
$queryStats: {
transformIdentifiers: {
algorithm: <string>,
hmacKey: <binData> /* subtype 8 - used for sensitive data */
}
}
}
]
} )

Important

You cannot run $queryStats on a specific collection. For complete examples, see Examples.

$queryStats takes the following fields:

Field
Necessity
Type
Description
transformIdentifiers
Optional
Document
Specifies additional transformation options for the $queryStats output.
transformIdentifiers
.algorithm
Required if specifying the transformIdentifiers object
String
The type of hash transformation applied to namespace information and field names in output. The only currently supported algorithm value is hmac-sha-256.
transformIdentifiers
.hmacKey
Required if specifying the transformIdentifiers object
binData
The private key input in the HMAC transformation.

If your deployment enforces access control, the user running $queryStats must have the following permissions:

  • To run $queryStats without the transformIdentifiers option, the user must have the queryStatsRead privilege action.

  • To run $queryStats with the transformIdentifiers option, the user must have the both the queryStatsRead and queryStatsReadTransformed privilege actions.

The built-in clusterMonitor role provides the queryStatsRead and queryStatsReadTransformed privileges. The following example grants the clusterMonitor role on the admin database:

db.grantRolesToUser(
"<user>",
[ { role: "clusterMonitor", db: "admin" } ]
)

The following sections describe behavioral details of the $queryStats stage.

Statistics for the $queryStats stage are tracked in a virtual collection that is stored in-memory. The memory limit for the virtual collection is 1% of the system's total memory.

$queryStats groups queries with common properties into the same output document. The resulting document is called a query stats entry.

$queryStats groups similar queries together by normalizing user-provided field values to their data types. For example, a filter specified as { item: 'card' } is normalized to { item : '?string'}. $queryStats also normalizes the values of some query options like hint and comment.

$queryStats preserves literal values for options like readConcern and readPreference.

For the complete list of options included in a query stats entry, see find Command Query Shape.

When an HMAC key is specified to the transformIdentifiers option, $queryStats uses the HMAC key to apply an HMAC-SHA-256 hash function on the following data:

  • Document field names

  • Collection names

  • Database names

$queryStats does not apply the HMAC transformation to the following data:

  • MQL keywords such as operator names (for example, $gte).

  • Parameter names such as the partitionBy parameter in $setWindowFields.

  • Field values. $queryStats normalizes field values in a query to their data types (such as number or string) when the query is recorded. $queryStats never stores field values that contain user data.

For an example of transformed output, see Transformed Example.

$queryStats returns an array of query stats entries. Some query stats entry properties contain literal values, and some properties are normalized to group common queries.

Query stats entries contain the following top-level documents:

Document
Description
key

The unique combination of attributes that define an entry in the query stats output. The key contains attributes such as:

Each unique combination of attributes creates a separate entry in the $queryStats virtual collection.

asOf
The UTC time when $queryStats read this entry from the $queryStats virtual collection. asOf does not necessarily return the same UTC time for each result. Internally, the data structure is partitioned, and each partition will be read at an individual point in time.
metrics
Contains aggregated runtime metrics associated with each query stats entry. Each query stats entry records statistics for each query that shares the same key.

Each document in the output array contains the following fields:

Field
Type
Literal or Normalized
Description
key
Document
Literal
Contains the query shape and additional query attributes that group a set of queries together
key.queryShape
Document
Literal
Contains attributes used to group similar queries together. For more information, see Query Shape.
key.client
Document
Literal
Describes client information associated with the key
key.client.application
Document
Literal
The client application name
key.client.driver
Document
Literal
Describes the driver used to issue the query
key.client.driver.name
String
Literal
Name of the driver used to issue the query. Possible values include mongosh and nodejs.
key.client.driver.version
String
Literal
Version number of the driver used to issue the query
key.client.os
Document
Literal
Describes the operating system used by the client that issued the query
key.client.os.type
String
Literal
Type of the operating system
key.client.os.name
String
Literal
Name of the operating system
key.client.os.architecture
String
Literal
Architecture of the operating system. Possible values include arm64 and x86_64.
key.client.os.version
String
Literal
Version number of the operating system
key.readConcern
Document
Literal
The read concern for the key
key.collectionType
String
Literal
The type of collection the query was issued on. For more information, see Collection Type.
key.hint
Document or String
Normalized
The index that was used as a hint for the query
key.batchSize
String
Normalized
The batch size for the key. Batch size specifies the number of documents to return in each batch of the response from the MongoDB instance.
key.comment
String
Normalized
Comment associated with the key
key.maxTimeMS
String
Normalized
maxTimeMS value associated with the key
key.noCursorTimeout
Boolean
Normalized
noCursorTimeout option associated with the key
key.allowPartialResults
String
Literal
allowPartialResults option associated with the key
key.readPreference
String
Literal
Read preference associated with the key
key.apiVersion
String
Literal
The Stable API version associated with the key. See Stable API.
key.apiStrict
Boolean
Literal
The apiStrict parameter value associated with the key. See Stable API Parameters.
key.apiDeprecationErrors
Boolean
Literal
The apiDeprecationErrors parameter value associated with the key. See Stable API Parameters.
metrics
Document
Literal
Describes runtime statistics for the key
metrics.lastExecutionMicros
NumberLong
Literal
Execution runtime for the most recent query for all queries with the given key
metrics.execCount
NumberLong
Literal
Number of times that queries with the given key have been executed
metrics.totalExecMicros
Document
Literal

Describes the total time spent running queries with the given key. If the query resulted in getMores, totalExecMicros includes the time spent processing the getMore requests. totalExecMicros does not include time spent waiting for the client.

All subfields of totalExecMicros are reported in microseconds.

metrics
.totalExecMicros
.sum
NumberLong
Literal
Total time spent running queries with the given key
metrics
.totalExecMicros
.max
NumberLong
Literal
Longest amount of time spent running a query with the given key
metrics
.totalExecMicros
.min
NumberLong
Literal
Shortest amount of time spent running a query with the given key
metrics
.totalExecMicros
.sumOfSquares
NumberLong
Literal
Sum of squares of the total execution times for all queries with the given key. A high sumOfSquares value indicates high variance in query execution times.
metrics
.firstResponseExecMicros
Document
Literal

Describes the time spent from when a query within they key began processing to when the server returns the first batch of results

All subfields of firstResponseExecMicros are reported in microseconds.

metrics
.firstResponseExecMicros
.sum
NumberLong
Literal
Combined amount of time spent from the beginning of query processing to when the server returns the first batch of results
metrics
.firstResponseExecMicros
.max
NumberLong
Literal
Longest amount of time spent from the beginning of query processing to when the server returns the first batch of results
metrics
.firstResponseExecMicros
.min
NumberLong
Literal
Shortest amount of time spent from the beginning of query processing to when the server returns the first batch of results
metrics
.firstResponseExecMicros
.sumOfSquares
NumberLong
Literal

Sum of squares of amounts of time spent from the beginning of query processing to when the server returns the first batch of results.

A high sumOfSquares value indicates high variance in query processing times.

metrics.docsReturned
Document
Literal
Describes the number of documents returned by queries within the key
metrics.docsReturned.sum
NumberLong
Literal
Total number of documents returned by queries with the given key
metrics.docsReturned.max
NumberLong
Literal
Maximum number of documents returned by a query with the given key
metrics.docsReturned.min
NumberLong
Literal
Fewest number of documents returned by a query with the given key
metrics
.docsReturned
.sumOfSquares
NumberLong
Literal

Sum of squares of number of documents returned by a query within the key.

A high sumOfSquares value indicates high variance in the number of documents returned between individual queries.

metrics.firstSeenTimestamp
Date
Literal
Time that a query with the given key was first used since the last restart
metrics.lastSeenTimestamp
Date
Literal
Time that a query with the given key was most recently used

The key.collectionType field indicates the type of collection that the recorded query was issued on. The collectionType can be one of the following values:

Field
Description
changeStream
The query was a change stream operation.
collection
The query was issued on a standard collection.
nonExistent
The query was issued on a collection that does not exist.
timeseries
The query was issued on a timeseries collection.
view
The query was issued on a view.
virtual

The query was issued on a virtual collection. The following operations occur in virtual collections:

The key.queryShape contains query attributes used to group similar queries together. The fields in key.queryShape vary based on the command that resulted in the query stats entry. $queryStats creates query stats entries for aggregate and find commands.

Each query shape property corresponds to a query option. For example, key.queryShape.sort corresponds to the sort() specification for the query shape.

The following table describes the query shape properties for find commands.

Field
Type
Literal or Normalized
key.queryShape.filter
Document
Normalized
key.queryShape.sort
Document
Literal
key.queryShape.projection
Document
Normalized
key.queryShape.skip
Integer
Normalized
key.queryShape.limit
Integer
Normalized
key.queryShape.singleBatch
Boolean
Literal
key.queryShape.max
Document
Normalized
key.queryShape.min
Document
Normalized
key.queryShape.returnKey
Boolean
Literal
key.queryShape.showRecordId
Boolean
Literal
key.queryShape.tailable
Boolean
Literal
key.queryShape.oplogReplay
Boolean
Literal
key.queryShape.awaitData
Boolean
Literal
key.queryShape.collation
Document
Literal
key.queryShape.allowDiskUse
Boolean
Literal
key.queryShape.let
Document
Normalized

The following table describes the query shape properties for aggregate commands.

Field
Type
Literal or Normalized
key.queryShape.pipeline
Array
Normalized
key.queryShape.explain
Boolean
Literal
key.queryShape.allowDiskUse
Boolean
Literal
key.queryShape.collation
Document
Literal
key.queryShape.hint
String or Document
Normalized
key.queryShape.let
Document
Normalized

To run the examples in this section, start with the following data:

db.products.insertMany(
[
{ item: "card", qty: 15 },
{ item: "envelope", qty: 20 },
{ item: "stamps" , qty: 30 }
]
)

Then, run these commands:

db.products.find( { item: "card" } )
db.products.aggregate( [
{
$match: { qty: { $gt: 20 } }
}
] )

The following examples show the output of $queryStats using different types of data transformation:

Input:

db.getSiblingDB("admin").aggregate( [
{
$queryStats: { }
}
] )

Output:

[
{
key: {
queryShape: {
cmdNs: { db: 'test', coll: 'products' },
command: 'find',
filter: { item: { '$eq': '?string' } }
},
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection'
},
metrics: {
lastExecutionMicros: Long("4254"),
execCount: Long("1"),
totalExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Long("18096516")
},
firstResponseExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Long("18096516")
},
docsReturned: {
sum: Long("1"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("1")
},
firstSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z"),
latestSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z")
},
asOf: Timestamp({ t: 1694695007, i: 0 })
},
{
key: {
queryShape: {
cmdNs: { db: 'test', coll: 'products' },
command: 'aggregate',
pipeline: [
{ '$match': { qty: { '$gt': '?number' } } }
]
},
apiVersion: '1',
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection',
cursor: { batchSize: '?number' }
},
metrics: {
lastExecutionMicros: Long("350"),
execCount: Long("3"),
totalExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Long("6422726")
},
firstResponseExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Long("6422726")
},
docsReturned: {
sum: Long("3"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("3")
},
firstSeenTimestamp: ISODate("2023-11-29T21:16:17.796Z"),
latestSeenTimestamp: ISODate("2023-11-29T21:17:12.385Z")
},
asOf: Timestamp({ t: 1701292827, i: 0 })
}
]

Input:

db.getSiblingDB("admin").aggregate( [
{
$queryStats: {
transformIdentifiers: {
algorithm: "hmac-sha-256" ,
hmacKey: BinData(8, "87c4082f169d3fef0eef34dc8e23458cbb457c3sf3n2")
}
}
}
] )

Output:

[
{
key: {
queryShape: {
cmdNs: {
db: 'Mtrt3iG7dsX5c5uCSIhSVlcu5qD3u3xx2EQnS1dJLxM=',
coll: '3oJE6AyOuf8h5NqWiXETxulFlPm3QUXbMnMjL2EqAU4='
},
command: 'find',
filter: {
'VWVRow7Ure92ajRPfrpWiU8OtDeWcLePFIq0+tooBng=': { '$eq': '?string' }
}
},
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection'
},
metrics: {
lastExecutionMicros: Long("4254"),
execCount: Long("1"),
totalExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Long("18096516")
},
firstResponseExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Long("18096516")
},
docsReturned: {
sum: Long("1"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("1")
},
firstSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z"),
latestSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z")
},
asOf: Timestamp({ t: 1694695712, i: 0 })
},
{
key: {
queryShape: {
cmdNs: {
db: 'Mtrt3iG7dsX5c5uCSIhSVlcu5qD3u3xx2EQnS1dJLxM=',
coll: '3oJE6AyOuf8h5NqWiXETxulFlPm3QUXbMnMjL2EqAU4='
},
command: 'aggregate',
pipeline: [
{
'$match': {
'RVqrwNEPotzdKnma/T7s4YcgNvpqO29BMDoni2N4IMI=': { '$gt': '?number' }
}
}
]
},
apiVersion: '1',
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection',
cursor: { batchSize: '?number' }
},
metrics: {
lastExecutionMicros: Long("350"),
execCount: Long("3"),
totalExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Long("6422726")
},
firstResponseExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Long("6422726")
},
docsReturned: {
sum: Long("3"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("3")
},
firstSeenTimestamp: ISODate("2023-11-29T21:16:17.796Z"),
latestSeenTimestamp: ISODate("2023-11-29T21:17:12.385Z")
},
asOf: Timestamp({ t: 1701293302, i: 0 })
},
]

MongoDB Atlas periodically uses $queryStats to collect anonymized data about your queries, which helps improve MongoDB products. Your data may also be used to make feature suggestions based on usage. MongoDB retains the data it collects with $queryStats for four years.

When Atlas runs $queryStats on your deployment, it uses a unique HMAC key per Atlas organization to transform your data and avoid collecting sensitive information.

←  $project (aggregation)$redact (aggregation) →