> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

# distinct (database command)

## Definition

Finds the distinct values for a specified field across a single collection. Returns a document containing an array of distinct values and an embedded document containing query statistics and the query plan.

**Tip:**

In [`mongosh`](https://www.mongodb.com/docs/mongodb-shell.md#std-program-mongosh), this command can also be run through the [`db.collection.distinct()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#mongodb-method-db.collection.distinct) helper method.

Helper methods are convenient for [`mongosh`](https://www.mongodb.com/docs/mongodb-shell.md#std-program-mongosh) users, but they may not return the same level of information as database commands.  In cases where the convenience is not needed or the additional return fields are required, use the database command.

## Compatibility

This command is available in deployments hosted in the following environments:

- [MongoDB Atlas](https://www.mongodb.com/docs/atlas): The fully managed service for MongoDB deployments in the cloud

**Important:**

This command has limited support in M0 and Flex clusters. For more information, see [Unsupported Commands.](https://www.mongodb.com/docs/atlas/unsupported-commands/)

- [MongoDB Enterprise](https://www.mongodb.com/docs/manual/administration/install-enterprise.md#std-label-install-mdb-enterprise): The subscription-based, self-managed version of MongoDB

- [MongoDB Community](https://www.mongodb.com/docs/manual/administration/install-community.md#std-label-install-mdb-community-edition): The source-available, free-to-use, and self-managed version of MongoDB

## Syntax

The command has the following syntax:

```javascript
db.runCommand(
   {
     distinct: "<collection>",
     key: "<field>",
     query: <query>,
     readConcern: <read concern document>,
     collation: <collation document>,
     comment: <any>,
     hint: <string or document>
   }
)
```

## Command Fields

The command takes the following fields:

| Field | Type | Description |
| --- | --- | --- |
| `distinct` | string | The name of the collection to query for distinct values. |
| `key` | string | The field for which to return distinct values. |
| `query` | document | Optional. A query that specifies the documents from which to retrieve the distinct values. |
| `readConcern` | document | Optional. Specifies the [read concern.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-read-concern) The `readConcern` option has the following syntax: `readConcern: { level: <value> }` Possible read concern levels are: [`"local"`](https://www.mongodb.com/docs/manual/reference/read-concern-local.md#mongodb-readconcern-readconcern.-local-). This is the default read concern level for read operations against the primary and secondaries.; [`"available"`](https://www.mongodb.com/docs/manual/reference/read-concern-available.md#mongodb-readconcern-readconcern.-available-). Available for read operations against the primary and secondaries. [`"available"`](https://www.mongodb.com/docs/manual/reference/read-concern-available.md#mongodb-readconcern-readconcern.-available-) behaves the same as [`"local"`](https://www.mongodb.com/docs/manual/reference/read-concern-local.md#mongodb-readconcern-readconcern.-local-) against the primary and non-sharded secondaries. The query returns the instance's most recent data.; [`"majority"`](https://www.mongodb.com/docs/manual/reference/read-concern-majority.md#mongodb-readconcern-readconcern.-majority-). Available for replica sets that use [WiredTiger storage engine.](https://www.mongodb.com/docs/manual/core/wiredtiger.md#std-label-storage-wiredtiger); [`"linearizable"`](https://www.mongodb.com/docs/manual/reference/read-concern-linearizable.md#mongodb-readconcern-readconcern.-linearizable-). Available for read operations on the [`primary`](https://www.mongodb.com/docs/manual/reference/replica-states.md#mongodb-replstate-replstate.PRIMARY) only.; [`"snapshot"`](https://www.mongodb.com/docs/manual/reference/read-concern-snapshot.md#mongodb-readconcern-readconcern.-snapshot-). Available for [multi-document transactions](https://www.mongodb.com/docs/manual/core/transactions.md#std-label-transactions) and certain read operations outside of multi-document transactions. For more formation on the read concern levels, see [Read Concern Levels.](https://www.mongodb.com/docs/manual/reference/read-concern.md#std-label-read-concern-levels) |
| `collation` | document | Optional. Optional. Specifies the [collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation)  to use for the operation. [Collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation) allows users to specify language-specific rules for string comparison, such as rules for lettercase and accent marks. The collation option has the following syntax: `collation: {
    locale: <string>,
    caseLevel: <boolean>,
    caseFirst: <string>,
    strength: <int>,
    numericOrdering: <boolean>,
    alternate: <string>,
    maxVariable: <string>,
    backwards: <boolean>
 }` When specifying collation, the `locale` field is mandatory; all other collation fields are optional. For descriptions of the fields, see [Collation Document.](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation-document-fields) If the collation is unspecified but the collection has a default collation (see [`db.createCollection()`](https://www.mongodb.com/docs/manual/reference/method/db.createCollection.md#mongodb-method-db.createCollection)), the operation uses the collation specified for the collection. If no collation is specified for the collection or for the operations, MongoDB uses the simple binary comparison used in prior versions for string comparisons. You cannot specify multiple collations for an operation. For example, you cannot specify different collations per field, or if performing a find with a sort, you cannot use one collation for the find and another for the sort. |
| `comment` | any | Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records of this command in the following locations: [mongod log messages](https://www.mongodb.com/docs/manual/reference/log-messages.md#std-label-log-messages-ref), in the `attr.command.cursor.comment` field.; [Database profiler](https://www.mongodb.com/docs/manual/reference/database-profiler.md#std-label-profiler) output, in the [`command.comment`](https://www.mongodb.com/docs/manual/reference/database-profiler.md#mongodb-data-system.profile.command) field.; [`currentOp`](https://www.mongodb.com/docs/manual/reference/command/currentOp.md#mongodb-dbcommand-dbcmd.currentOp) output, in the [`command.comment`](https://www.mongodb.com/docs/manual/reference/command/currentOp.md#mongodb-data-currentOp.command) field. A comment can be any valid [BSON type](https://www.mongodb.com/docs/manual/reference/bson-types.md#std-label-bson-types) (string, integer, object, array, etc). |
| `hint` | string or document | Optional. Specify the index name, either as a string or a document. If specified, the query planner only considers plans using the hinted index. For more details, see [Specify an Index.](https://www.mongodb.com/docs/manual/reference/command/distinct.md#std-label-index-example) **New in version 7.1** |

**Note:**

Results must not be larger than the maximum [BSON size](https://www.mongodb.com/docs/manual/reference/limits.md#std-label-limit-bson-document-size). If your results exceed the maximum BSON size, use the aggregation pipeline to retrieve distinct values using the [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) operator, as described in [Retrieve Distinct Values with the Aggregation Pipeline.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#std-label-aggregation-group-distinct-values)

For the equivalent [`mongosh`](https://www.mongodb.com/docs/mongodb-shell.md#std-program-mongosh) method, see [`db.collection.distinct()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#mongodb-method-db.collection.distinct). For driver-specific wrapper methods, see the [driver documentation.](https://www.mongodb.com/docs/drivers/)

## Behavior

In a [sharded cluster](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-sharded-cluster), the [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) command may return [orphaned documents.](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-orphaned-document)

For [time series collections](https://www.mongodb.com/docs/manual/core/timeseries-collections.md#std-label-manual-timeseries-landing), the `distinct` command can't make efficient use of indexes. Instead, use a [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) aggregation to group documents by distinct values. For details, see [Time Series Limitations.](https://www.mongodb.com/docs/manual/core/timeseries/timeseries-limitations.md#std-label-timeseries-limitation-distinct)

### Array Fields

If the value of the specified `field` is an array, [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) considers each element of the array as a separate value.

For instance, if a field has as its value `[ 1, [1], 1 ]`, then [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) considers `1`, `[1]`, and `1` as separate values.

Starting in MongoDB 6.0, the [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) command returns the same results for collections and [views](https://www.mongodb.com/docs/manual/core/views.md#std-label-views-landing-page) when using arrays.

For examples, see:

- [Return Distinct Values for an Array Field](https://www.mongodb.com/docs/manual/reference/command/distinct.md#std-label-distinct-command-array)

- [Arrays in Collections and Views](https://www.mongodb.com/docs/manual/reference/command/distinct.md#std-label-distinct-arrays-in-collections-and-views)

### Index Use

When possible, [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) operations can use indexes.

Indexes can also [cover](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-covered-queries) [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) operations. See [Run Covered Queries](https://www.mongodb.com/docs/manual/core/query-optimization.md#std-label-covered-queries) for more information on queries covered by indexes.

### Transactions

To perform a distinct operation within a transaction:

- For unsharded collections, you can use the [`db.collection.distinct()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#mongodb-method-db.collection.distinct) method/the [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) command as well as the aggregation pipeline with the [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage.

- For sharded collections, you cannot use the [`db.collection.distinct()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.distinct.md#mongodb-method-db.collection.distinct) method or the [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) command.

  To find the distinct values for a sharded collection, use the aggregation pipeline with the [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage instead. See [Distinct Operation](https://www.mongodb.com/docs/manual/core/transactions-operations.md#std-label-transactions-operations-distinct) for details.

**Important:**

In most cases, a distributed transaction incurs a greater performance cost over single document writes, and the availability of distributed transactions should not be a replacement for effective schema design. For many scenarios, the [denormalized data model (embedded documents and arrays)](https://www.mongodb.com/docs/manual/data-modeling/embedding.md#std-label-data-modeling-embedding) will continue to be optimal for your data and use cases. That is, for many scenarios, modeling your data appropriately will minimize the need for distributed transactions.

For additional transactions usage considerations (such as runtime limit and oplog size limit), see also [Production Considerations.](https://www.mongodb.com/docs/manual/core/transactions-production-consideration.md#std-label-production-considerations)

### Client Disconnection

If the client that issued [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) disconnects before the operation completes, MongoDB marks [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) for termination using [`killOp`.](https://www.mongodb.com/docs/manual/reference/command/killOp.md#mongodb-dbcommand-dbcmd.killOp)

### Replica Set Member State Restriction

To run on a replica set member, [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) operations require the member to be in [`PRIMARY`](https://www.mongodb.com/docs/manual/reference/replica-states.md#mongodb-replstate-replstate.PRIMARY) or [`SECONDARY`](https://www.mongodb.com/docs/manual/reference/replica-states.md#mongodb-replstate-replstate.SECONDARY) state. If the member is in another state, such as [`STARTUP2`](https://www.mongodb.com/docs/manual/reference/replica-states.md#mongodb-replstate-replstate.STARTUP2), the operation errors.

### Index Filters and Collations

Starting in MongoDB 6.0, an index filter uses the [collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation) previously set using the [`planCacheSetFilter`](https://www.mongodb.com/docs/manual/reference/command/planCacheSetFilter.md#mongodb-dbcommand-dbcmd.planCacheSetFilter) command.

Starting in MongoDB 8.0, use query settings instead of adding [index filters](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-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. To add query settings and explore examples, see [`setQuerySettings`.](https://www.mongodb.com/docs/manual/reference/command/setQuerySettings.md#mongodb-dbcommand-dbcmd.setQuerySettings)

### Query Settings

**New in version 8.0**

You can use query settings to set index hints, set [operation rejection filters](https://www.mongodb.com/docs/manual/tutorial/operation-rejection-filters.md#std-label-operation-rejection-filters), and other fields. The settings apply to the [query shape](https://www.mongodb.com/docs/manual/reference/operator/aggregation/queryStats.md#std-label-queryStats-queryShape) on the entire cluster. The cluster retains the settings after shutdown.

The [query optimizer](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-query-optimizer) uses the query settings as an additional input during query planning, which affects the plan selected to run the query. You can also use query settings to block a query shape.

To add query settings and explore examples, see [`setQuerySettings`.](https://www.mongodb.com/docs/manual/reference/command/setQuerySettings.md#mongodb-dbcommand-dbcmd.setQuerySettings)

You can add query settings for [`find`](https://www.mongodb.com/docs/manual/reference/command/find.md#mongodb-dbcommand-dbcmd.find), [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct), and [`aggregate`](https://www.mongodb.com/docs/manual/reference/command/aggregate.md#mongodb-dbcommand-dbcmd.aggregate) commands.

Query settings have more functionality and are preferred over deprecated [index filters.](https://www.mongodb.com/docs/manual/core/query-plans.md#std-label-index-filters)

To remove query settings, use [`removeQuerySettings`](https://www.mongodb.com/docs/manual/reference/command/removeQuerySettings.md#mongodb-dbcommand-dbcmd.removeQuerySettings). To obtain the query settings, use a [`$querySettings`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/querySettings.md#mongodb-pipeline-pipe.-querySettings) stage in an aggregation pipeline.

## Examples

The examples use the `inventory` collection that contains the following documents:

```javascript
{ "_id": 1, "dept": "A", "item": { "sku": "111", "color": "red" }, "sizes": [ "S", "M" ] }
{ "_id": 2, "dept": "A", "item": { "sku": "111", "color": "blue" }, "sizes": [ "M", "L" ] }
{ "_id": 3, "dept": "B", "item": { "sku": "222", "color": "blue" }, "sizes": "S" }
{ "_id": 4, "dept": "A", "item": { "sku": "333", "color": "black" }, "sizes": [ "S" ] }
```

### Return Distinct Values for a Field

The following example returns distinct `dept` values from the `inventory` collection:

```javascript
db.runCommand ( { distinct: "inventory", key: "dept" } )
```

The command returns a document with a `values` field containing the distinct `dept` values:

```javascript
{
   "values" : [ "A", "B" ],
   "ok" : 1
}
```

### Return Distinct Values for an Embedded Field

The following example returns distinct values for the embedded field `item.sku` from the `inventory` collection:

```javascript
db.runCommand ( { distinct: "inventory", key: "item.sku" } )
```

The command returns a document with a `values` field containing the distinct `sku` values:

```javascript
{
  "values" : [ "111", "222", "333" ],
  "ok" : 1
}
```

**See also:**

[Dot Notation](https://www.mongodb.com/docs/manual/core/document.md#std-label-document-dot-notation) for information on accessing fields within embedded documents

### Return Distinct Values for an Array Field

The following example returns distinct `sizes` values from the `inventory` collection:

```javascript
db.runCommand ( { distinct: "inventory", key: "sizes" } )
```

The command returns a document with a `values` field containing the distinct `sizes` values:

```javascript
{
  "values" : [ "M", "S", "L" ],
  "ok" : 1
}
```

For information on [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) and array fields, see the [Behavior](https://www.mongodb.com/docs/manual/reference/command/distinct.md#std-label-distinct-command-array-behavior) section.

### Arrays in Collections and Views

Starting in MongoDB 6.0, the [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) command returns the same results for collections and [views](https://www.mongodb.com/docs/manual/core/views.md#std-label-views-landing-page) when using arrays.

The following example creates a collection named `sensor` with an array of temperature values for each document:

```javascript
db.sensor.insertMany( [
   { _id: 0, temperatures: [ { value: 1 }, { value: 4 } ] },
   { _id: 1, temperatures: [ { value: 2 }, { value: 8 } ] },
   { _id: 2, temperatures: [ { value: 3 }, { value: 12 } ] },
   { _id: 3, temperatures: [ { value: 1 }, { value: 4 } ] }
] )
```

The following example creates a view named `sensorView` from the `sensor` collection:

```javascript
db.createView( "sensorView", "sensor", [] )
```

The following example uses [`distinct`](https://www.mongodb.com/docs/manual/reference/command/distinct.md#mongodb-dbcommand-dbcmd.distinct) to return the unique values from the `temperatures` array in the `sensor` collection:

```javascript
db.sensor.distinct( "temperatures.1.value" )
```

The `1` in `temperatures.1.value` specifies the `temperatures` array index.

Example output:

```javascript
[ 4, 8, 12 ]
```

Example for `sensorView`:

```javascript
db.sensorView.distinct( "temperatures.1.value" )
```

Example output:

- `[ 4, 8, 12 ]` starting in MongoDB 6.0 (identical to result returned from the `sensor` collection).

- `[]` in MongoDB versions earlier than 6.0.

### Specify Query with `distinct`

The following example returns distinct values for the embedded `item.sku` field where `dept` equals `"A"`:

```javascript
db.runCommand ( { distinct: "inventory", key: "item.sku", query: { dept: "A"} } )
```

The command returns a document with a `values` field containing the distinct `sku` values:

```javascript
{
  "values" : [ "111", "333" ],
  "ok" : 1
}
```

### Specify a Collation

[Collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation) allows users to specify language-specific rules for string comparison, such as rules for lettercase and accent marks.

A collection `myColl` has the following documents:

```javascript
{ _id: 1, category: "café", status: "A" }
{ _id: 2, category: "cafe", status: "a" }
{ _id: 3, category: "cafE", status: "a" }
```

The following aggregation operation includes the [Collation](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation) option:

```javascript
db.runCommand(
   {
      distinct: "myColl",
      key: "category",
      collation: { locale: "fr", strength: 1 }
   }
)
```

For descriptions on the collation fields, see [Collation Document.](https://www.mongodb.com/docs/manual/reference/collation.md#std-label-collation-document-fields)

### Override Default Read Concern

To override the default read concern level of [`"local"`](https://www.mongodb.com/docs/manual/reference/read-concern-local.md#mongodb-readconcern-readconcern.-local-), use the `readConcern` option.

The following operation on a replica set specifies a [read concern](https://www.mongodb.com/docs/manual/reference/read-concern.md#std-label-read-concern) of [`"majority"`](https://www.mongodb.com/docs/manual/reference/read-concern-majority.md#mongodb-readconcern-readconcern.-majority-) to read the most recent copy of the data confirmed as having been written to a majority of the nodes.

**Note:**

Regardless of the [read concern](https://www.mongodb.com/docs/manual/reference/glossary.md#std-term-read-concern) level, the most recent data on a node may not reflect the most recent version of the data in the system.

```javascript
db.runCommand(
   {
     distinct: "restaurants",
     key: "rating",
     query: { cuisine: "italian" },
     readConcern: { level: "majority" }
   }
)
```

To ensure that a single thread can read its own writes, use [`"majority"`](https://www.mongodb.com/docs/manual/reference/read-concern-majority.md#mongodb-readconcern-readconcern.-majority-) read concern and [`"majority"`](https://www.mongodb.com/docs/manual/reference/write-concern.md#mongodb-writeconcern-writeconcern.-majority-) write concern against the primary of the replica set.

### Specify an Index

You can specify an index name or pattern using the hint option.

To specify a hint based on an index name:

```javascript
db.runCommand ( { distinct: "inventory", key: "dept", hint: "sizes" } )
```

To specify a hint based on an index pattern:

```javascript
db.runCommand ( { distinct: "inventory", key: "dept", hint: { sizes: 1 } } )
```