$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
- $queryStats Log Entries
- Output
- Collection Type
- Query Shape
- Examples
- Untransformed Example
- Transformed Example
- MongoDB Atlas Data Collection
New in version 7.0.12: (Also available starting in 6.0.7)
Definition
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()
, find()
,
and distinct()
queries. $queryStats
does
not collect information for queries that use Queryable Encryption.
Requirements
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.
Syntax
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.
Command Fields
$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. |
Access Control
If your deployment enforces access control, the user running
$queryStats
must have the following permissions:
To run
$queryStats
without thetransformIdentifiers
option, the user must have thequeryStatsRead
privilege action.To run
$queryStats
with thetransformIdentifiers
option, the user must have the both thequeryStatsRead
andqueryStatsReadTransformed
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" } ] )
Behavior
The following sections describe behavioral details of the
$queryStats
stage.
How $queryStats Tracks Query Statistics
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.
How $queryStats Groups Returned Documents
$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.
How $queryStats Transforms Data Using transformIdentifiers
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 Log Entries
MongoDB records $queryStats
operations in the deployment logs. By default, MongoDB only logs the invocation of
$queryStats
operations, not the operation's output. For
$queryStats
operations that include the transformIdentifiers
option, you can specify whether the transformed output is included in
the log entry.
To learn how to control $queryStats
logging behavior, see
Toggle $queryStats Log Output.
Output
$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
Each unique combination of attributes creates a separate
entry in the |
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. |
keyHash | String | Literal | A hashed representation of the values in the key . Each unique
keyHash value corresponds to a unique entry in the
$queryStats memory store. |
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.keysExamined | Document | Literal | Describes the number of keys examined by queries |
metrics .keysExamined .sum | Integer | Literal | Total number of keys examined |
metrics .keysExamined .max | NumberLong | Literal | Maximum number of keys examined |
metrics .keysExamined .min | NumberLong | Literal | Fewest number of keys examined |
metrics .keysExamined .sumOfSquares | NumberDecimal | Literal | Sum of squares of number of keys examined. A high |
metrics.docsExamined | Document | Literal | Describes the number of documents examined by queries |
metrics .docsExamined .sum | Integer | Literal | Total number of documents examined in the query |
metrics .docsExamined .max | NumberLong | Literal | Maximum number of documents examined |
metrics .docsExamined .min | NumberLong | Literal | Minimum number of documents examined |
metrics .docsExamined .sumOfSquares | NumberDecimal | Literal | Sum of squares of number of documents examined. A high |
metrics.hasSortStage | Boolean | Literal | true when MongoDB must sort the documents after it receives
the documents from a cursor. |
metrics.usedDisk | Boolean | Literal | true when the query writes data to temporary files due to
memory restrictions. |
metrics.fromMultiPlanner | Boolean | Literal | true when the query planner evaluates multiple plans before
choosing the winning execution plan for the query. |
metrics.fromPlanCache | Boolean | Literal | true when the query planner is able to use a plan from the
plan cache. |
metrics.totalExecMicros | Document | Literal | Describes the total time spent running queries with the given
key. If the query resulted in All subfields of |
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 | NumberDecimal | 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 |
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 | NumberDecimal | 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 |
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 | NumberDecimal | Literal | Sum of squares of number of documents returned by a query within the key. A high |
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 |
Collection Type
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: |
Query Shape
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
, find
, and
distinct
commands.
Each query shape property corresponds to a query option. For example,
key.queryShape.sort
corresponds to the sort()
specification for the query shape.
find Command 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 |
aggregate Command Query Shape
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 |
distinct Command Query Shape
The following table describes the query shape properties for
distinct
commands.
Field | Type | Literal or Normalized |
---|---|---|
key.queryShape.key | String | Literal |
key.queryShape.collation | Document | Normalized |
key.queryShape.query | Document | Normalized |
Examples
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:
Untransformed Example
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' }, keyHash: 'dsoJ+LHAru0z6MJ1/IygJnnLTrlpVYYmPnlmNZbZrLI=', metrics: { lastExecutionMicros: Long("4254"), execCount: Long("1"), totalExecMicros: { sum: Long("4254"), max: Long("4254"), min: Long("4254"), sumOfSquares: Decimal128("18096516") }, firstResponseExecMicros: { sum: Long("4254"), max: Long("4254"), min: Long("4254"), sumOfSquares: Decimal128("18096516") }, docsReturned: { sum: Long("1"), max: Long("1"), min: Long("1"), sumOfSquares: Decimal128("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' } }, keyHash: '2QLBfL0m1lliStdN4XvBjqVBtZQ6ffaB2L1pJ99twT8=', metrics: { lastExecutionMicros: Long("350"), execCount: Long("3"), totalExecMicros: { sum: Long("3084"), max: Long("2499"), min: Long("235"), sumOfSquares: Decimal128("6422726") }, firstResponseExecMicros: { sum: Long("3084"), max: Long("2499"), min: Long("235"), sumOfSquares: Decimal128("6422726") }, docsReturned: { sum: Long("3"), max: Long("1"), min: Long("1"), sumOfSquares: Decimal128("3") }, firstSeenTimestamp: ISODate("2023-11-29T21:16:17.796Z"), latestSeenTimestamp: ISODate("2023-11-29T21:17:12.385Z") }, asOf: Timestamp({ t: 1701292827, i: 0 }) } ]
Transformed Example
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' }, keyHash: 'q4vxam+wbk8tTrl8D0MDFH1LQAbI8fWspfkGKhEUROk=', metrics: { lastExecutionMicros: Long("4254"), execCount: Long("1"), keysExamined: { sum: Int("5"), max: Long("5"), min: Long("5"), sumOfSquares: Decimal128("25") }, docsExamined: { sum: Long("1"), max: Long("1"), min: Long("1"), sumOfSquares: Decimal128("1") }, hasSortStage: false, usedDisk: false, fromMultiPlanner: false, fromPlanCache: true, totalExecMicros: { sum: Long("4254"), max: Long("4254"), min: Long("4254"), sumOfSquares: Decimal128("18096516") }, firstResponseExecMicros: { sum: Long("4254"), max: Long("4254"), min: Long("4254"), sumOfSquares: Decimal128("18096516") }, docsReturned: { sum: Long("1"), max: Long("1"), min: Long("1"), sumOfSquares: Decimal128("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' } }, keyHash: 'HEhpQTYB+/wVoHLkOkMd+EC2jguQlMJ1N/vTE7+b8Js=', metrics: { lastExecutionMicros: Long("350"), execCount: Long("3"), keysExamined: { sum: Int("5"), max: Long("5"), min: Long("5"), sumOfSquares: Decimal128("25") }, docsExamined: { sum: Long("1"), max: Long("1"), min: Long("1"), sumOfSquares: Decimal128("1") }, hasSortStage: false, usedDisk: false, fromMultiPlanner: false, fromPlanCache: true, totalExecMicros: { sum: Long("3084"), max: Long("2499"), min: Long("235"), sumOfSquares: Decimal128("6422726") }, firstResponseExecMicros: { sum: Long("3084"), max: Long("2499"), min: Long("235"), sumOfSquares: Decimal128("6422726") }, docsReturned: { sum: Long("3"), max: Long("1"), min: Long("1"), sumOfSquares: Decimal128("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 Data Collection
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.