K Engle
K Engle

Reputation: 1562

Is there a better way of doing case insensitive sort in MongoDB without shadow columns?

I have several objects I need to perform a case insensitive sort on. I've been told I cannot add shadow columns to the database to normalize values. Due to this I am using aggregation to sort my output.

I have a simple object represented as such:

_id: The ObjectID

createdOn: Time object was created

lastUpdatedOn: Time object was last updated

name: The name of the object

isActive: Boolean value representing if the object is currently active

This is my aggregate function:

db.organization.aggregate(
    [
        {
            $match: { isActive: true }
        },
        {
            $project: {
                      lastUpdatedOn: 1,
                      createdOn: 1,
                      name: 1,
                      normalizedName: {$toLower: "$name"}
                      }
        },
        {
            $sort: { normalizedName: 1 }
        },
        {
            $project: {
                      lastUpdatedOn: 1,
                      createdOn: 1,
                      name: 1
                      }
        }
    ]
)

It first ensures that we are only dealing with active objects, creates the normalized field for our sort, performs the sort, then drops the normalized field so it isn't exposed to the user. It works and puts all the objects in alphabetical order based on the name field, but I don't know if there is a better way of doing it.

Upvotes: 1

Views: 1351

Answers (3)

tbmpls
tbmpls

Reputation: 231

I realize that this is an old question but since Mongo 3.4 Collation is supported which makes this much easier. For Aggregation with Collation just append a collation object like this: db.organization.aggregate([pipelineobjs],{collation: {locale:'en_US'}}).

I believe your specific aggregation would look like:

db.organization.aggregate(
    [
        {
            $match: { isActive: true }
        },
        {
            $project: {
                      lastUpdatedOn: 1,
                      createdOn: 1,
                      name: 1
                      }
        },
        {
            $sort: { name: 1 }
        },
        {
            $project: {
                      lastUpdatedOn: 1,
                      createdOn: 1,
                      name: 1
                      }
        }
    ],
    {collation: {locale:'en_US'}}
)

Upvotes: 0

phpMax
phpMax

Reputation: 149

Although MondoDB does not offer it, but you can very easily do it in PHP or any other language. Here is how to do it in PHP.

$cn = new MongoClient($dbHost);
$db = $cn->selectDB($dbName);
$col = new MongoCollection($db, $collectionName);

$cursor = $col->find();
$cursor = iterator_to_array($cursor);

foreach ($cursor as $key => $row) {
    $name[$key] = $row['name'];
    $email[$key] = $row['email'];
}
//$name is the field to sort on, taken from the above loop
//You can use SORT_ASC or SORT_DESC

array_multisort($name, SORT_ASC, $cursor);

foreach ($cursor as $doc) {
    echo $doc['name'].'-'.$doc['email'].'<br/>';
}

Upvotes: 0

JohnnyHK
JohnnyHK

Reputation: 311905

It's not necessarily a dramatic improvement, but depending on your specific use case, projecting $$ROOT to capture the original doc separate from the temporary sorting column can be cleaner:

db.organization.aggregate(
    [
        {
            $match: { isActive: true }
        },
        {
            $project: {
                      doc: '$$ROOT'
                      normalizedName: {$toLower: "$name"}
                      }
        },
        {
            $sort: { normalizedName: 1 }
        },
        {
            $project: { doc: 1 }
        }
    ]
)

The downside is you end up with your original doc off in a doc property, but that's easily cleaned up client-side via something like Array#map. Or you can modify the final $project to promote each field to the top-level, but that can be tedious.

Upvotes: 2

Related Questions