distinct

Definition

• distinct
• Finds the distinct values for a specified field across a singlecollection. distinct returns a document that containsan array of the distinct values. The return document also containsan embedded document with query statistics and the query plan.

The command takes the following form

{
  distinct: "<collection>",
  key: "<field>",
  query: <query>,
  readConcern: <read concern document>,
  collation: <collation document>
}

The command contains the following fields:

FieldTypeDescriptiondistinctstringThe name of the collection to query for distinct values.keystringThe field for which to return distinct values.querydocumentOptional. A query that specifies the documents from which to retrieve thedistinct values.readConcerndocumentOptional. Specifies the read concern.

Starting in MongoDB 3.6, the readConcern option has the followingsyntax: readConcern: { level: <value> }

Possible read concern levels are:

• "local". This is the default read concern level forread operations against primary and read operations againstsecondaries when associated with causally consistent sessions.
• "available". This is the default for reads againstsecondaries when when not associated with causally consistentsessions. The query returns the instance’s mostrecent data.
• "majority". Available for replica sets that useWiredTiger storage engine.
• "linearizable". Available for read operations on theprimary only.For more formation on the read concern levels, seeRead Concern Levels.collationdocumentOptional.

Specifies the collation to use for the operation.

Collation allows users to specifylanguage-specific rules for string comparison, such as rules forlettercase 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; allother collation fields are optional. For descriptions of the fields,see Collation Document.

If the collation is unspecified but the collection has adefault collation (see db.createCollection()), theoperation uses the collation specified for the collection.

If no collation is specified for the collection or for theoperations, MongoDB uses the simple binary comparison used in priorversions for string comparisons.

You cannot specify multiple collations for an operation. Forexample, you cannot specify different collations per field, or ifperforming a find with a sort, you cannot use one collation for thefind and another for the sort.

New in version 3.4.

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.

MongoDB also provides the shell wrapper methoddb.collection.distinct() for the distinctcommand. Additionally, many MongoDB driversprovide a wrapper method. Refer to the specific driver documentation.

Behavior

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

Array Fields

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

For instance, if a field has as its value [ 1, [1], 1 ], thendistinct considers 1, [1], and 1 as separate values.

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

Index Use

When possible, distinct operations can use indexes.

Indexes can also coverdistinct 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 distinctdisconnects before the operation completes, MongoDB marksthe 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.runCommand ( { distinct: "inventory", key: "dept" } )

The command returns a document with a field named values thatcontains the distinct dept values:

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

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.runCommand ( { distinct: "inventory", key: "item.sku" } )

The command returns a document with a field named values thatcontains the distinct sku values:

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

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.runCommand ( { distinct: "inventory", key: "sizes" } )

The command returns a document with a field named values thatcontains the distinct sizes values:

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

For information on distinct and array fields, see theBehavior 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.runCommand ( { distinct: "inventory", key: "item.sku", query: { dept: "A"} } )

The command returns a document with a field named values thatcontains the distinct sku values:

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

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.runCommand(
   {
      distinct: "myColl",
      key: "category",
      collation: { locale: "fr", strength: 1 }
   }
)

For descriptions on the collation fields, seeCollation Document.

Override Default Read Concern

To override the default read concern level of "local",use the readConcern option.

The following operation on a replica set specifies aRead Concern of "majority" to read themost recent copy of the data confirmed as having been written to amajority of the nodes.

Note

• To use read concern level of "majority", replicasets must use WiredTiger storage engine.

You can disable read concern "majority" for a deploymentwith a three-member primary-secondary-arbiter (PSA) architecture;however, this has implications for change streams (in MongoDB 4.0 andearlier only) and transactions on sharded clusters. For more information,see Disable Read Concern Majority.

• Regardless of the read concern level, the most recent data on anode may not reflect the most recent version of the data in the system.

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" read concern and "majority"write concern against the primary of the replica set.