Reputation: 1562
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
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
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
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