- Reference >
- Database Commands >
- Aggregation Commands >
- distinct
distinct¶
On this page
Definition¶
-
distinct
¶ Finds the distinct values for a specified field across a single collection.
distinct
returns a document that contains an array of the distinct values. The return document also contains an embedded document with query statistics and the query plan.The command takes the following form
The command contains 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.
The readConcern option has the following syntax:
Changed in version 3.6.
Possible read concern levels are:
"local"
. This is the default read concern level."available"
. This is the default for reads against secondaries when Read Operations and Causally Consistent Sessions and “level” are unspecified. The query returns the instance’s most recent data."majority"
. Available for replica sets that use WiredTiger storage engine."linearizable"
. Available for read operations on theprimary
only.
For more formation on the read concern levels, see Read Concern Levels.
For
"local"
(default) or"majority"
read concern level, you can specify theafterClusterTime
option to have the read operation return data that meets the level requirement and the specified after cluster time requirement. For more information, see Read Operations and Causally Consistent Sessions.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:
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.
New in version 3.4.
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.MongoDB also provides the shell wrapper method
db.collection.distinct()
for thedistinct
command. Additionally, many MongoDB drivers provide a wrapper method. Refer to the specific driver documentation.
Behavior¶
In a sharded cluster, the distinct
command may return
orphaned documents.
Array Fields¶
If the value of the specified field
is an array,
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
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 cover
distinct
operations. See Covered Query for more information
on queries covered by indexes.
Examples¶
The examples use the inventory
collection that contains the
following documents:
Return Distinct Values for a Field¶
The following example returns the distinct values for the field
dept
from all documents in the inventory
collection:
The command returns a document with a field named values
that
contains the distinct dept
values:
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:
The command returns a document with a field named values
that
contains the distinct sku
values:
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:
The command returns a document with a field named values
that
contains the distinct sizes
values:
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"
:
The command returns a document with a field named values
that
contains the distinct sku
values:
Specify a Collation¶
New in version 3.4.
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:
The following aggregation operation includes the Collation option:
For descriptions on the collation fields, see Collation 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 a
Read Concern of "majority"
to read the
most recent copy of the data confirmed as having been written to a
majority of the nodes.
Note
To use read concern level of
"majority"
, replica sets must use WiredTiger storage engine and electionprotocol version 1
.Starting in MongoDB 3.6, support for read concern
"majority"
is enabled by default. For MongoDB 3.6.1 - 3.6.x, you can disable read concern"majority"
. For more information, see Disable Read Concern Majority.Regardless of the read concern level, the most recent data on a node may not reflect the most recent version of the data in the system.
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.