How to sort by string on the base of alphabets order and not by case?

Strange behaviour of sorting on string field,

Sample Documents:

[
  { "firstName": "A" },
  { "firstName": "b" },
  { "firstName": "C" },
  { "firstName": "d" },
  { "firstName": "e" },
  { "firstName": "A" }
]

Expected: I want sort it in Ascending order, exact like:

[
  { "firstName": "A" },
  { "firstName": "A" },
  { "firstName": "b" },
  { "firstName": "C" },
  { "firstName": "d" },
  { "firstName": "e" }
]

Query:

db.collection.aggregate([{ "$sort": { "firstName": 1 } }]);

But it gives sorting on the base of string case like upper case comes first and then lower case.
Above query gives:

[
  { "firstName": "A" },
  { "firstName": "A" },
  { "firstName": "C" },
  { "firstName": "b" },
  { "firstName": "d" },
  { "firstName": "e" }
]

Is there any way to manage this situation?

I know i can add one more stage before sort stage to convert string in upper case or lower case to get order in sequence.

2 Likes

Hello @turivishal, you can use collation and specify the caseLevel option to get the desired result. For example,

db.test.find().sort({ firstName: 1 }).collation({ locale: "en", caseLevel: true })

For aggregate method you can specify the collation option.

3 Likes

Thank you @Prasad_Saya,

Helpful Document:

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.