Mongodb Alphabetical Sorting

Working with Data
,
11

Hi @Ashutosh_Mishra1,

As @Doug_Duncan mentioned, aggregation would be a straightforward way to dynamically calculate a field for custom sort orders but I would definitely add a pre-calculated field to your documents with an appropriate Index to Support Sorted Query Results. You want to avoid the performance overhead and limitations of an in-memory sort.

I set up some test data:

db.company.insert([
	{ InvestorName: '#Angels'},
	{ InvestorName: '&Vest'},
	{ InvestorName: '+Impact'},
	{ InvestorName: '0 Ventures'},
	{ InvestorName: 'A Plus Finance'}
])

I used $trim to remove ignorable characters and $toLower to make a case-insensitive pre-calculated sort field:

db.company.aggregate([

	// This field could also be calculated in client code
	// when documents are inserted or updated

	{ $addFields: {
		sortOrder: {
			$toLower: {
				$trim: {
					input: "$InvestorName",
					chars: "#&*+1234567890 "
				}
			}
		}
	}},

	// Update collection with calculated sortOrder field
	{ $out: "company" }
])

Now I can get the expected order using my pre-calculated custom sortOrder field (and fast, with an appropriate index):

db.company.find({}, {_id:0}).sort({sortOrder:1})	

[
  { InvestorName: 'A Plus Finance', sortOrder: 'a plus finance' },
  { InvestorName: '#Angels', sortOrder: 'angels' },
  { InvestorName: '+Impact', sortOrder: 'impact' },
  { InvestorName: '0 Ventures', sortOrder: 'ventures' },
  { InvestorName: '&Vest', sortOrder: 'vest' }
]

The sortOrder field could be projected out, but I included it here to show the outcome of my earlier calculation.

Regards,
Stennie

3 Likes