How to perform case-insensitive aggregation grouping in MongoDb?

Question

Let's say that I want to aggregate and group by documents in MongoDb by the Description field.

Running the following (case-sensitive by default):

db['Products'].aggregate(
    { $group: { 
        _id: { 'Description': "$Description" },
        count: { $sum: 1 },
        docs: { $push: "$_id" }
    }},
    { $match: {
        count: { $gt : 1 }
    }}
);

on my sample data gives me 1000 results, which is fine.

But now I expect that running a case-insensitive query (using $toLower) should give me less than or equal to 1000 results:

db['Products'].aggregate(
    { $group: { 
        _id: { 'Description': {$toLower: "$Description"} },
        count: { $sum: 1 },
        docs: { $push: "$_id" }
    }},
    { $match: {
        count: { $gt : 1 }
    }}
);

But instead I get more than 1000 results. That can't be right, can it? More common entries should get grouped together to yield less number of total groupings ... I think.

So then probably my aggregation query is wrong! Which brings me to my question:

How should case-insensitive aggregation grouping in MongoDb be performed?

Answer 1

You approach to case-insensitive grouping is correct so perhaps your observation is not? ;)

Try this example:

// insert two documents
db.getCollection('test').insertOne({"name" : "Test"}) // uppercase 'T'
db.getCollection('test').insertOne({"name" : "test"}) // lowercase 't'

// perform the grouping
db.getCollection('test').aggregate({ $group: { "_id": { $toLower: "$name" }, "count": { $sum: 1 } } }) // case insensitive
db.getCollection('test').aggregate({ $group: { "_id": "$name", "count": { $sum: 1 } } }) // case sensitive

You may have a typo somewhere?

The documentation also states that

$toLower only has a well-defined behavior for strings of ASCII characters.

Perhaps that's what's biting you here?