Create a Collection with Collation

Limitations

The following restrictions apply when the parameter numericOrdering is set to true:

• Only contiguous non-negative integer substrings of digits are considered in the comparisons. numericOrdering does not support:

  • +

  • -

  • exponents

• Only Unicode code points in the Number or Decimal Digit (Nd) category are treated as digits.

• If the number length exceeds 254 characters, the excess characters are treated as a separate number.

Procedure

Example

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

[
  { "n": "1" },
  { "n": "2" },
  { "n": "-2.1" },
  { "n": "2.0" },
  { "n": "2.20" },
  { "n": "10"},
  { "n": "20" },
  { "n": "20.1" },
  { "n": "-10" },
  { "n": "3" }
]

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
} )

For more information on querying documents in Compass, see Query Your Data.

The operations returns the following results:

[
   { "n": "-2.1" },
   { "n": "-10" },
   { "n": "1" },
   { "n": "2" },
   { "n": "2.0" }
   { "n": "2.20" },
   { "n": "3" },
   { "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.