Collation (original) (raw)

Collation allows users to specify language-specific rules for string comparison, such as rules for lettercase and accent marks.

You can specify collation for a collection or a view, an index, or specific operations that support collation.

To specify collation when you query documents in the MongoDB Atlas UI, seeSpecify Collation.

A collation document has the following fields:

{

   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.

Default collation parameter values vary depending on which locale you specify. For a complete list of default collation parameters and the locales they are associated with, seeCollation Default Parameters.

You can specify collation for the following operations:

Note

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.

Some collation locales have variants, which employ special language-specific rules. To specify a locale variant, use the following syntax:

{ "locale" : "<locale code>@collation=<variant>" }

For example, to use the unihan variant of the Chinese collation:

{ "locale" : "zh@collation=unihan" }

For a complete list of all collation locales and their variants, seeCollation Locales.

• You can specify a default collationfor a view at creation time. If no collation is specified, the view's default collation is the "simple" binary comparison collator. That is, the view does not inherit the collection's default collation.
• String comparisons on the view use the view's default collation. An operation that attempts to change or override a view's default collation will fail with an error.
• If creating a view from another view, you cannot specify a collation that differs from the source view's collation.
• If performing an aggregation that involves multiple views, such as with $lookup or $graphLookup, the views must have the same collation.

To use an index for string comparisons, an operation must also specify the same collation. That is, an index with a collation cannot support an operation that performs string comparisons on the indexed fields if the operation specifies a different collation.

Warning

Because indexes that are configured with collation use ICU collation keys to achieve sort order, collation-aware index keys may be larger than index keys for indexes without collation.

A restaurants collection has the following documents:

db.restaurants.insertMany( [

   { _id: 1, category: "café", status: "Open" },

   { _id: 2, category: "cafe", status: "open" },

   { _id: 3, category: "cafE", status: "open" }

] )

The restaurants collection has an index on a string fieldcategory with the collation locale "fr".

db.restaurants.createIndex( { category: 1 }, { collation: { locale: "fr" } } )

The following query, which specifies the same collation as the index, can use the index:

db.restaurants.find( { category: "cafe" } ).collation( { locale: "fr" } )

However, the following query operation, which by default uses the "simple" binary collator, cannot use the index:

db.restaurants.find( { category: "cafe" } )

For a compound index where the index prefix keys are not strings, arrays, and embedded documents, an operation that specifies a different collation can still use the index to support comparisons on the index prefix keys.

For example, the collection restaurants has a compound index on the numeric fields score and price and the string fieldcategory; the index is created with the collation locale"fr" for string comparisons:

db.restaurants.createIndex(

   { score: 1, price: 1, category: 1 },

   { collation: { locale: "fr" } } )

The following operations, which use "simple" binary collation for string comparisons, can use the index:

db.restaurants.find( { score: 5 } ).sort( { price: 1 } )

db.restaurants.find( { score: 5, price: { $gt: NumberDecimal( "10" ) } } ).sort( { price: 1 } )

The following operation, which uses "simple" binary collation for string comparisons on the indexed category field, can use the index to fulfill only the score: 5 portion of the query:

db.restaurants.find( { score: 5, category: "cafe" } )

To confirm whether a query used an index, run the query with theexplain() option.

Important

Matches against document keys, including embedded document keys, use simple binary comparison. This means that a query for a key like "type.café" will not match the key "type.cafe", regardless of the value you set for the strength parameter.

The following indexes only support simple binary comparison and do not support collation:

• Text indexes
• 2d indexes

Tip

To create a text or 2d index on a collection that has a non-simple collation, you must explicitly specify {collation: {locale: "simple"} } when creating the index.

When specifying the numericOrdering as true the following restrictions apply:

• Only contiguous non-negative integer substrings of digits are considered in the comparisons.
  numericOrdering does not support:
  • +
  • -
  • decimal separators, like decimal points and decimal commas
  • exponents
• Only Unicode code points in the Number or Decimal Digit (Nd) category are treated as digits.
• If a digit length exceeds 254 characters, the excess characters are treated as a separate number.

Consider a collection with the following string number and decimal values:

db.c.insertMany(

  [

      { "n" : "1" },

      { "n" : "2" },

      { "n" : "2.1" },

      { "n" : "-2.1" },

      { "n" : "2.2" },

      { "n" : "2.10" },

      { "n" : "2.20" },

      { "n" : "-10" },

      { "n" : "10" },

      { "n" : "20" },

      { "n" : "20.1" }

  ]

)

The following find query uses a collation document containing the numericOrdering parameter:

db.c.find(

   { }, { _id: 0 }

).sort(

  { n: 1 }

).collation( {

  locale: 'en_US',

  numericOrdering: true

} )

The operation returns the following results:

[

    { n: '-2.1' },

    { n: '-10' },

    { n: '1' },

    { n: '2' },

    { n: '2.1' },

    { n: '2.2' },

    { n: '2.10' },

    { n: '2.20' },

    { n: '10' },

    { n: '20' },

    { n: '20.1' }

]

• numericOrdering: true sorts the string values in ascending order as if they were numeric values.
• The two negative values -2.1 and -10 are not sorted in the expected sort order because they have unsupported - characters.
• The value 2.2 is sorted before the value 2.10, due to the fact that the numericOrdering parameter does not support decimal values.
• As a result, 2.2 and 2.10 are sorted in lexicographic order.

A restaurants collection has the following documents:

db.restaurants.insertMany( [

   { _id: 1, category: "café", status: "Open" },

   { _id: 2, category: "cafe", status: "open" },

   { _id: 3, category: "cafE", status: "open" }

] )

The following find() operation uses collation:

db.restaurants.find(

   { category: "cafe", status: "Open" }

).collation( { locale: "fr", strength: 1 } )

[

   { _id: 1, category: 'café', status: 'Open' },

   { _id: 2, category: 'cafe', status: 'open' },

   { _id: 3, category: 'cafE', status: 'open' }

]

The filter specifies a collation with strength: 1, which means the query ignores differences between case and letter variants. As a result, even though there is not a document that has an exact match with the specified case and letter variants in the filter, the operation returns all documents in the collection.