Docs Menu
Docs Home
/
MongoDB Manual
/
Reference
/
mongosh Methods
/
Collections

db.collection.distinct()

On this page

  • Definition
  • Compatibility
  • Syntax
  • Options
  • Behavior
  • Examples

MongoDB with drivers

This page documents a mongosh method. To see the equivalent method in a MongoDB driver, see the corresponding page for your programming language:

C#Java SyncNode.jsPyMongoC++GoJava RSKotlin CoroutineKotlin SyncPHPMongoidRustScala

Definition

db.collection.distinct(field, query, options)

Finds the distinct values for a specified field across a single collection or view and returns the results in an array.

Compatibility

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

  • MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

Important

This command has limited support in M0, M2, and M5 clusters. For more information, see Unsupported Commands.

  • MongoDB Enterprise: The subscription-based, self-managed version of MongoDB

  • MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

Syntax

This method takes the following parameters:

Parameter
Type
Description
field
string
The field for which to return distinct values.
query
document
A query that specifies the documents from which to retrieve the distinct values.
options
document
Optional. A document that specifies the options. See Options.

Note

Results must not be larger than the maximum BSON size. If your results exceed the maximum BSON size, use the aggregation pipeline to retrieve distinct values using the $group operator, as described in Retrieve Distinct Values with the Aggregation Pipeline.

The following diagram shows an example db.collection.distinct() call.

Diagram of the annotated distinct operation.

Options

{ collation: <document> }
Field
Type
Description
collation
document

Optional.

Specifies the collation to use for the operation.

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.

If the collation is unspecified but the collection has a default collation (see 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.

Behavior

In a sharded cluster, the distinct command may return orphaned documents.

For time series collections, the distinct command can't make efficient use of indexes. Instead, use a $group aggregation to group documents by distinct values. For details, see Time Series Limitations.

Array Fields

If the value of the specified field is an array, db.collection.distinct() considers each element of the array as a separate value.

For instance, if a field has as its value [ 1, [1], 1 ], then db.collection.distinct() considers 1, [1], and 1 as separate values.

For an example, see Return Distinct Values for an Array Field.

Index Use

When possible, db.collection.distinct() operations can use indexes.

Indexes can also cover db.collection.distinct() operations. See Covered Query for more information on queries covered by indexes.

Transactions

To perform a distinct operation within a transaction:

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

Client Disconnection

Starting in MongoDB 4.2, if the client that issued db.collection.distinct() disconnects before the operation completes, MongoDB marks db.collection.distinct() for termination using killOp.

Replica Set Member State Restriction

To run on a replica set member, distinct operations require the member to be in PRIMARY or SECONDARY state. If the member is in another state, such as STARTUP2, the operation errors.

Query Settings

New in version 8.0.

You can use query settings to set index hints, set operation rejection filters, and other fields. The settings apply to the query shape on the entire cluster. The cluster retains the settings after shutdown.

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

You can add query settings for find, distinct, and aggregate commands.

Query settings have more functionality and are preferred over deprecated index filters.

To remove query settings, use removeQuerySettings. To obtain the query settings, use a $querySettings stage in an aggregation pipeline.

Examples

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

{ "_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 the distinct values for the field dept from all documents in the inventory collection:

db.inventory.distinct( "dept" )

The method returns the following array of distinct dept values:

[ "A", "B" ]

Return Distinct Values for an Embedded Field

The following example returns the distinct values for the field sku, embedded in the item field, from all documents in the inventory collection:

db.inventory.distinct( "item.sku" )

The method returns the following array of distinct sku values:

[ "111", "222", "333" ]

Tip

See also:

Dot Notation for information on accessing fields within embedded documents

Return Distinct Values for an Array Field

The following example returns the distinct values for the field sizes from all documents in the inventory collection:

db.inventory.distinct( "sizes" )

The method returns the following array of distinct sizes values:

[ "M", "S", "L" ]

For information on distinct() and array fields, see the Behavior section.

Specify Query with distinct

The following example returns the distinct values for the field sku, embedded in the item field, from the documents whose dept is equal to "A":

db.inventory.distinct( "item.sku", { dept: "A" } )

The method returns the following array of distinct sku values:

[ "111", "333" ]

Specify a Collation

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:

{ _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 option:

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

For descriptions on the collation fields, see Collation Document.

Back

db.collection.deleteOne

Next

db.collection.drop