/ /

Retrieve Distinct Field Values

Overview

In this guide, you can learn how to use the .NET/C# Driver to retrieve the distinct values of a specified field across a collection.

Within a collection, different documents might contain different values for a single field. For example, one document in a restaurants collection has a borough value of "Manhattan", and another has a borough value of "Queens". By using the .NET/C# Driver, you can retrieve all the unique values that a field contains across multiple documents in a collection.

Sample Data

The examples in this guide use the sample_restaurants.restaurants collection from the Atlas sample datasets. To learn how to create a free MongoDB Atlas cluster and load the sample datasets, see the Get Started with the .NET/C# Driver.

The examples on this page uses the following Restaurant class to model the documents in the collection:

public class Restaurant {
    public ObjectId? Id { get; set; }
    [BsonElement("name")]
    public string? Name { get; set; }
    [BsonElement("cuisine")]
    public string? Cuisine { get; set; }
    [BsonElement("borough")]
    public string? Borough { get; set; }
}

Retrieve Distinct Values

To retrieve the distinct values for a specified field, call the Distinct() or DistinctAsync() method of an IMongoCollection<TDocument> instance and pass the name of the field you want to find distinct values for.

Retrieve Values Across a Collection

The following example retrieves the distinct values of the borough field in the restaurants collection. Select the Synchronous or Asynchronous tab to see the corresponding code.

var results = collection.Distinct<string>(r => r.Borough, Builders<Restaurant>.Filters.Empty).ToList();
foreach (var result in results)
{
    Console.WriteLine(result);
}

Bronx
Brooklyn
Manhattan
Missing
Queens
Staten Island

var results = await collection.DistinctAsync<string>(r => r.Borough, Builders<Restaurant>.Filters.Empty);
await results.ForEachAsync(result => Console.WriteLine(result));

Bronx
Brooklyn
Manhattan
Missing
Queens
Staten Island

The operation returns a cursor that you can iterate through to access each distinct borough field value. Although several documents have the same value in the borough field, each value appears in the results only once.

Retrieve Values Across Specified Documents

You can provide a query filter to the Distinct() and DistinctAsync() methods to find distinct field values within a subset of documents in a collection. A query filter is an expression that specifies search criteria used to match documents in an operation. For more information about creating a query filter, see the Create a Query Filter guide.

The following example retrieves the distinct values of the borough field for all documents that have a cuisine field value of "Italian". Select the Synchronous or Asynchronous tab to see the corresponding code.

var filter = Builders<Restaurant>.Filter.Eq(r => r.Cuisine, "Italian");
var results = collection.Distinct<string>(r => r.Borough, filter).ToList();
foreach (var result in results)
{
    Console.WriteLine(result);
}

Bronx
Brooklyn
Manhattan
Queens
Staten Island

var filter = Builders<Restaurant>.Filter.Eq(r => r.Cuisine, "Italian");
var results = await collection.DistinctAsync<string>(r => r.Borough, filter);
await results.ForEachAsync(result => Console.WriteLine(result));

Bronx
Brooklyn
Manhattan
Queens
Staten Island

Modify Distinct Behavior

You can modify the behavior of the Distinct() and DistinctAsync() methods by providing a DistinctOptions instance as an optional parameter. The following table describes the properties you can set on a DistinctOptions instance:

Method	Description
`Collation`	Sets the collation to use for the operation. See the Collation section of this page for more information. Default: `null` Data type: Collation
`MaxTime`	Sets the maximum amount of time that the operation can run. Data type: `TimeSpan`
`Comment`	Attaches a comment to the operation. Data type: BsonValue or `string`

The following example retrieves the distinct values of the name field for all documents that have a borough field value of "Bronx" and a cuisine field value of "Pizza". Then, it adds a comment to the operation by providing a DistinctOptions instance to the Distinct() method.

Select the Synchronous or Asynchronous tab to see the corresponding code.

var cuisineFilter = Builders<Restaurant>.Filter.Eq(r => r.Cuisine, "Pizza");
var boroughFilter = Builders<Restaurant>.Filter.Eq(r => r.Borough, "Bronx");
var filter = Builders<Restaurant>.Filter.And(cuisineFilter, boroughFilter);
var options = new DistinctOptions {
    Comment = "Find all Italian restaurants in the Bronx"
};
var results = collection.Distinct<string>(r => r.Name, filter).ToList();
foreach (var result in results)
{
    Console.WriteLine(result);
}

$1.25 Pizza
18 East Gunhill Pizza
2 Bros
Aenos Pizza
Alitalia Pizza Restaurant
Amici Pizza And Pasta
Angie'S Cafe Pizza
...

var cuisineFilter = Builders<Restaurant>.Filter.Eq(r => r.Cuisine, "Pizza");
var boroughFilter = Builders<Restaurant>.Filter.Eq(r => r.Borough, "Bronx");
var filter = Builders<Restaurant>.Filter.And(cuisineFilter, boroughFilter);
var options = new DistinctOptions {
    Comment = "Find all Italian restaurants in the Bronx"
};
var results = await collection.DistinctAsync<string>(r => r.Name, filter, options);
await results.ForEachAsync(result => Console.WriteLine(result));

$1.25 Pizza
18 East Gunhill Pizza
2 Bros
Aenos Pizza
Alitalia Pizza Restaurant
Amici Pizza And Pasta
Angie'S Cafe Pizza
...

Collation

To configure collation for your operation, create an instance of the Collation class.

The following table describes the parameters that the Collation constructor accepts. It also lists the corresponding class property that you can use to read each setting's value.

Parameter	Description	Class Property
`locale`	Specifies the International Components for Unicode (ICU) locale. For a list of supported locales, see Collation Locales and Default Parameters in the MongoDB Server Manual. If you want to use simple binary comparison, use the `Collation.Simple` static property to return a `Collation` object with the `locale` set to `"simple"`. Data Type: `string`	`Locale`
`caseLevel`	(Optional) Specifies whether to include case comparison. When this argument is `true`, the driver's behavior depends on the value of the `strength` argument: - If `strength` is `CollationStrength.Primary`, the driver compares base characters and case. - If `strength` is `CollationStrength.Secondary`, the driver compares base characters, diacritics, other secondary differences, and case. - If `strength` is any other value, this argument is ignored. When this argument is `false`, the driver doesn't include case comparison at strength level `Primary` or `Secondary`. Data Type: `boolean` Default: `false`	`CaseLevel`
`caseFirst`	(Optional) Specifies the sort order of case differences during tertiary level comparisons. Data Type: CollationCaseFirst Default: `CollationCaseFirst.Off`	`CaseFirst`
`strength`	(Optional) Specifies the level of comparison to perform, as defined in the ICU documentation. Data Type: CollationStrength Default: `CollationStrength.Tertiary`	`Strength`
`numericOrdering`	(Optional) Specifies whether the driver compares numeric strings as numbers. If this argument is `true`, the driver compares numeric strings as numbers. For example, when comparing the strings "10" and "2", the driver treats the values as 10 and 2, and finds 10 to be greater. If this argument is `false` or excluded, the driver compares numeric strings as strings. For example, when comparing the strings "10" and "2", the driver compares one character at a time. Because "1" is less than "2", the driver finds "10" to be less than "2". For more information, see Collation Restrictions in the MongoDB Server manual. Data Type: `boolean` Default: `false`	`NumericOrdering`
`alternate`	(Optional) Specifies whether the driver considers whitespace and punctuation as base characters for purposes of comparison. Data Type: CollationAlternate Default: `CollationAlternate.NonIgnorable` (spaces and punctuation are considered base characters)	`Alternate`
`maxVariable`	(Optional) Specifies which characters the driver considers ignorable when the `alternate` argument is `CollationAlternate.Shifted`. Data Type: CollationMaxVariable Default: `CollationMaxVariable.Punctuation` (the driver ignores punctuation and spaces)	`MaxVariable`
`normalization`	(Optional) Specifies whether the driver normalizes text as needed. Most text doesn't require normalization. For more information about normalization, see the ICU documentation. Data Type: `boolean` Default: `false`	`Normalization`
`backwards`	(Optional) Specifies whether strings containing diacritics sort from the back of the string to the front. Data Type: `boolean` Default: `false`	`Backwards`

For more information about collation, see the Collation page in the MongoDB Server manual.

API Documentation

To learn more about any of the methods or types discussed in this guide, see the following API documentation:

Back

Count Documents

Access Data from a Cursor