MongoDB 3.4聚合查询实现大小写不敏感排序的方法

I see exactly what's going on here—MongoDB's default string sorting uses case-sensitive comparison, which is why all uppercase entries get prioritized over lowercase ones. Let's fix this with two straightforward approaches, starting with the most efficient one:

Approach 1: Use collation (Recommended) #

MongoDB 3.4+ supports collation, which lets you define custom string comparison rules including case insensitivity. You can apply this either at the aggregate level (affecting all string operations in your pipeline) or directly in the $sort stage.

Modified Aggregation Pipeline #

Update your pipeline to include collation settings in the $sort stage (or pass it as an option to the aggregate() call):

var partnerCollection = [
  { $lookup: { 
      from: "courses", 
      localField: "_id", 
      foreignField: "partnerId", 
      as: "courses" 
    } 
  },
  { $project: { 
      "_id":1, 
      "partnerName":1, 
      "phone":1, 
      "email":1, 
      "courses.courseName": 1, 
      "courses.courseType":1 
    } 
  },
  { $match: findUserDataCondition },
  { $sort: { "partnerName" : 1 }, collation: { locale: "en", strength: 2 } }, // Added collation rule
  { $skip: start },
  { $limit: length }
];

// Alternatively, apply collation at the aggregate call level
db.partners.aggregate(partnerCollection, { collation: { locale: "en", strength: 2 } });

• locale: "en": Uses English language rules for string comparison.
• strength: 2: Ignores case differences while respecting character accents—perfect for your desired sorting behavior.

Approach 2: Project a Lowercase Field for Sorting (Legacy Compatibility) #

If you're working with a MongoDB version older than 3.4, you can create a temporary lowercase version of partnerName, sort by that field, then exclude it from the final output:

Modified Pipeline #

var partnerCollection = [
  { $lookup: { 
      from: "courses", 
      localField: "_id", 
      foreignField: "partnerId", 
      as: "courses" 
    } 
  },
  { $project: { 
      "_id":1, 
      "partnerName":1, 
      "phone":1, 
      "email":1, 
      "courses.courseName": 1, 
      "courses.courseType":1,
      "partnerNameLower": { $toLower: "$partnerName" } // Add temporary lowercase field
    } 
  },
  { $match: findUserDataCondition },
  { $sort: { "partnerNameLower" : 1 } }, // Sort using the lowercase field
  { $project: { "partnerNameLower": 0 } }, // Remove temporary field from output
  { $skip: start },
  { $limit: length }
];

How This Works #

• $toLower converts partnerName to all lowercase, creating a neutral field for case-insensitive sorting.
• After sorting, we exclude partnerNameLower to keep your final output clean and matching your original structure.

Verification #

Either approach will produce your desired sorted order: ABC, bcd, cde, DEF, EFG, since the comparison no longer prioritizes uppercase characters over lowercase ones.

内容的提问来源于stack exchange，提问作者SHIVAM SHARMA