db.collection.distinct()

Definition

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

mongo Shell Method

This page documents the mongo shell method, and doesnot refer to the MongoDB Node.js driver (or any other driver)method. For corresponding MongoDB driver API, refer to your specificMongoDB driver documentation instead.

Finds the distinctvalues for a specified field across a single collection or view and returnsthe results in an array.

ParameterTypeDescriptionfieldstringThe field for which to return distinct values.querydocumentA query that specifies the documents from which to retrieve the distinct values.optionsdocumentOptional. A document that specifies the options. See Options.

The db.collection.distinct() method provides a wrapperaround the distinct command.

Note

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

Options

{ collation: <document> }

collation: {   locale: <string>,   caseLevel: <boolean>,   caseFirst: <string>,   strength: <int>,   numericOrdering: <boolean>,   alternate: <string>,   maxVariable: <string>,   backwards: <boolean>}

Behavior

In a sharded cluster, the distinct command may returnorphaned documents.

Array Fields

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

For instance, if a field has as its value [ 1, [1], 1 ], thendb.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 coverdb.collection.distinct() operations. See Covered Query for more informationon queries covered by indexes.

Transactions

To perform a distinct operation within a transaction:

• For unsharded collections, you can use thedb.collection.distinct() method/thedistinct command as well as the aggregation pipelinewith the $group stage.

• For sharded collections, you cannot use thedb.collection.distinct() method or thedistinct command.

To find the distinct values for a sharded collection, use theaggregation pipeline with the $group stage instead.See Distinct Operation for details.

Important

In most cases, multi-document transaction incurs a greaterperformance cost over single document writes, and theavailability of multi-document transactions should not be areplacement for effective schema design. For many scenarios, thedenormalized data model (embedded documents and arrays) will continue to be optimal for yourdata and use cases. That is, for many scenarios, modeling your dataappropriately will minimize the need for multi-documenttransactions.

For additional transactions usage considerations(such as runtime limit and oplog size limit), see alsoProduction Considerations.

Client Disconnection

Starting in MongoDB 4.2, if the client that issued the db.collection.distinct()disconnects before the operation completes, MongoDB marksthe db.collection.distinct() for termination (i.e. killOp on theoperation).

Examples

The examples use the inventory collection that contains thefollowing 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 fielddept 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 fieldsku, embedded in the item field, from all documents in theinventory collection:

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

The method returns the following array of distinct sku values:

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

See also

Dot Notation for information on accessing fieldswithin embedded documents

Return Distinct Values for an Array Field

The following example returns the distinct values for the fieldsizes 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 arrayfields, see the Behavior section.

Specify Query with distinct

The following example returns the distinct values for the fieldsku, embedded in the item field, from the documents whosedept 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

New in version 3.4.

Collation allows users to specifylanguage-specific rules for string comparison, such as rules forlettercase 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 Collationoption:

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

For descriptions on the collation fields, seeCollation Document.