$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

Definition

$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 only collects and reports metrics for find() 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 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" } ]
)

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 readPreferences.

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.

Output

$queryStats returns an array of query stats entries. 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: Query shape Client information Read concern Collection type 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`	Object	Literal	Contains the query shape and additional query attributes that group a set of queries together
`key.queryShape`	Object	Literal	Contains attributes used to group similar queries together. For more information, see Query Shape.
`key.client`	Object	Literal	Describes client information associated with the key
`key.client.application`	Object	Literal	The client application name
`key.client.driver`	Object	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`	Object	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`	Object	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`	Object 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`	Object	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

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: `$currentOp` `$documents` `$listLocalSessions` `$queryStats`

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. Currently, $queryStats only creates query stats entries for find commands.

find Command Query Shape

The following table describes the query shape properties for find commands. Each property corresponds to a query option. For example, key.queryShape.sort corresponds to the sort() specification for the query shape.

Field	Type	Literal or Normalized
`key.queryShape.filter`	Object	Normalized
`key.queryShape.sort`	Object	Literal
`key.queryShape.projection`	Object	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`	bool	Literal
`key.queryShape.showRecordId`	bool	Literal
`key.queryShape.tailable`	bool	Literal
`key.queryShape.oplogReplay`	bool	Literal
`key.queryShape.awaitData`	bool	Literal
`key.queryShape.collation`	Document	Literal
`key.queryShape.allowDiskUse`	bool	Literal
`key.queryShape.let`	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 the following find() commands:

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

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

Untransformed Example
Transformed Example

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'
    },
    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: 'find',
        filter: { qty: { '$gt': '?number' } }
      },
      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("580"),
      execCount: Long("1"),
      totalExecMicros: {
        sum: Long("580"),
        max: Long("580"),
        min: Long("580"),
        sumOfSquares: Long("336400")
      },
      firstResponseExecMicros: {
        sum: Long("580"),
        max: Long("580"),
        min: Long("580"),
        sumOfSquares: Long("336400")
      },
      docsReturned: {
        sum: Long("1"),
        max: Long("1"),
        min: Long("1"),
        sumOfSquares: Long("1")
      },
      firstSeenTimestamp: ISODate("2023-09-14T12:30:52.476Z"),
      latestSeenTimestamp: ISODate("2023-09-14T12:30:52.476Z")
    },
    asOf: Timestamp({ t: 1694695007, i: 0 })
  },
]

Transformed Example