I have a problem with sorting. I need to sort by fields f0 , f1 , f2 , but at the same time do not take into account the case of the character, and records that do not have a field - put at the end of the list.

Here are the test data:

 db.getCollection("test").insert({f0: "A", f1: "A", f2: "A"}); db.getCollection("test").insert({f0: "A", f1: "A", f2: "a"}); db.getCollection("test").insert({f0: "A", f1: "A", f2: "B"}); db.getCollection("test").insert({f0: "A", f1: "A", f2: "b"}); db.getCollection("test").insert({f0: "A", f1: "A"}); db.getCollection("test").insert({f0: "A", f1: "a", f2: "A"}); db.getCollection("test").insert({f0: "A", f1: "a", f2: "a"}); db.getCollection("test").insert({f0: "A", f1: "a", f2: "B"}); db.getCollection("test").insert({f0: "A", f1: "a", f2: "b"}); db.getCollection("test").insert({f0: "A", f1: "a"}); db.getCollection("test").insert({f0: "A", f1: "B", f2: "A"}); db.getCollection("test").insert({f0: "A", f1: "B", f2: "a"}); db.getCollection("test").insert({f0: "A", f1: "B", f2: "B"}); db.getCollection("test").insert({f0: "A", f1: "B", f2: "b"}); db.getCollection("test").insert({f0: "A", f1: "B"}); db.getCollection("test").insert({f0: "A", f1: "b", f2: "A"}); db.getCollection("test").insert({f0: "A", f1: "b", f2: "a"}); db.getCollection("test").insert({f0: "A", f1: "b", f2: "B"}); db.getCollection("test").insert({f0: "A", f1: "b", f2: "b"}); db.getCollection("test").insert({f0: "A", f1: "b"}); db.getCollection("test").insert({f0: "A"}); db.getCollection("test").insert({f0: "a", f1: "A", f2: "A"}); db.getCollection("test").insert({f0: "a", f1: "A", f2: "a"}); db.getCollection("test").insert({f0: "a", f1: "A", f2: "B"}); db.getCollection("test").insert({f0: "a", f1: "A", f2: "b"}); db.getCollection("test").insert({f0: "a", f1: "A"}); db.getCollection("test").insert({f0: "a", f1: "a", f2: "A"}); db.getCollection("test").insert({f0: "a", f1: "a", f2: "a"}); db.getCollection("test").insert({f0: "a", f1: "a", f2: "B"}); db.getCollection("test").insert({f0: "a", f1: "a", f2: "b"}); db.getCollection("test").insert({f0: "a", f1: "a"}); db.getCollection("test").insert({f0: "a", f1: "B", f2: "A"}); db.getCollection("test").insert({f0: "a", f1: "B", f2: "a"}); db.getCollection("test").insert({f0: "a", f1: "B", f2: "B"}); db.getCollection("test").insert({f0: "a", f1: "B", f2: "b"}); db.getCollection("test").insert({f0: "a", f1: "B"}); db.getCollection("test").insert({f0: "a", f1: "b", f2: "A"}); db.getCollection("test").insert({f0: "a", f1: "b", f2: "a"}); db.getCollection("test").insert({f0: "a", f1: "b", f2: "B"}); db.getCollection("test").insert({f0: "a", f1: "b", f2: "b"}); db.getCollection("test").insert({f0: "a", f1: "b"}); db.getCollection("test").insert({f0: "a"}); db.getCollection("test").insert({f0: "B", f1: "A", f2: "A"}); db.getCollection("test").insert({f0: "B", f1: "A", f2: "a"}); db.getCollection("test").insert({f0: "B", f1: "A", f2: "B"}); db.getCollection("test").insert({f0: "B", f1: "A", f2: "b"}); db.getCollection("test").insert({f0: "B", f1: "A"}); db.getCollection("test").insert({f0: "B", f1: "a", f2: "A"}); db.getCollection("test").insert({f0: "B", f1: "a", f2: "a"}); db.getCollection("test").insert({f0: "B", f1: "a", f2: "B"}); db.getCollection("test").insert({f0: "B", f1: "a", f2: "b"}); db.getCollection("test").insert({f0: "B", f1: "a"}); db.getCollection("test").insert({f0: "B", f1: "B", f2: "A"}); db.getCollection("test").insert({f0: "B", f1: "B", f2: "a"}); db.getCollection("test").insert({f0: "B", f1: "B", f2: "B"}); db.getCollection("test").insert({f0: "B", f1: "B", f2: "b"}); db.getCollection("test").insert({f0: "B", f1: "B"}); db.getCollection("test").insert({f0: "B", f1: "b", f2: "A"}); db.getCollection("test").insert({f0: "B", f1: "b", f2: "a"}); db.getCollection("test").insert({f0: "B", f1: "b", f2: "B"}); db.getCollection("test").insert({f0: "B", f1: "b", f2: "b"}); db.getCollection("test").insert({f0: "B", f1: "b"}); db.getCollection("test").insert({f0: "B"}); db.getCollection("test").insert({f0: "b", f1: "A", f2: "A"}); db.getCollection("test").insert({f0: "b", f1: "A", f2: "a"}); db.getCollection("test").insert({f0: "b", f1: "A", f2: "B"}); db.getCollection("test").insert({f0: "b", f1: "A", f2: "b"}); db.getCollection("test").insert({f0: "b", f1: "A"}); db.getCollection("test").insert({f0: "b", f1: "a", f2: "A"}); db.getCollection("test").insert({f0: "b", f1: "a", f2: "a"}); db.getCollection("test").insert({f0: "b", f1: "a", f2: "B"}); db.getCollection("test").insert({f0: "b", f1: "a", f2: "b"}); db.getCollection("test").insert({f0: "b", f1: "a"}); db.getCollection("test").insert({f0: "b", f1: "B", f2: "A"}); db.getCollection("test").insert({f0: "b", f1: "B", f2: "a"}); db.getCollection("test").insert({f0: "b", f1: "B", f2: "B"}); db.getCollection("test").insert({f0: "b", f1: "B", f2: "b"}); db.getCollection("test").insert({f0: "b", f1: "B"}); db.getCollection("test").insert({f0: "b", f1: "b", f2: "A"}); db.getCollection("test").insert({f0: "b", f1: "b", f2: "a"}); db.getCollection("test").insert({f0: "b", f1: "b", f2: "B"}); db.getCollection("test").insert({f0: "b", f1: "b", f2: "b"}); db.getCollection("test").insert({f0: "b", f1: "b"}); db.getCollection("test").insert({f0: "b"}); db.getCollection("test").insert({ f1: "A", f2: "A"}); db.getCollection("test").insert({ f1: "A", f2: "a"}); db.getCollection("test").insert({ f1: "A", f2: "B"}); db.getCollection("test").insert({ f1: "A", f2: "b"}); db.getCollection("test").insert({ f1: "A"}); db.getCollection("test").insert({ f1: "a", f2: "A"}); db.getCollection("test").insert({ f1: "a", f2: "a"}); db.getCollection("test").insert({ f1: "a", f2: "B"}); db.getCollection("test").insert({ f1: "a", f2: "b"}); db.getCollection("test").insert({ f1: "a"}); db.getCollection("test").insert({ f1: "B", f2: "A"}); db.getCollection("test").insert({ f1: "B", f2: "a"}); db.getCollection("test").insert({ f1: "B", f2: "B"}); db.getCollection("test").insert({ f1: "B", f2: "b"}); db.getCollection("test").insert({ f1: "B"}); db.getCollection("test").insert({ f1: "b", f2: "A"}); db.getCollection("test").insert({ f1: "b", f2: "a"}); db.getCollection("test").insert({ f1: "b", f2: "B"}); db.getCollection("test").insert({ f1: "b", f2: "b"}); db.getCollection("test").insert({ f1: "b"}); db.getCollection("test").insert({});

Here is the request itself:

 db.getCollection('test').find({}).sort({"f0": 1,"f1": 1,"f2": 1});

But the conclusion, and it shows that the empty values ​​he pulls up, then there are capital letters, and then small. I need, first the letter a|A , b|B and only then empty values.

 /* 1 */ { "_id" : ObjectId("564d88d97b9999b45267d737") } /* 2 */ { "_id" : ObjectId("564d88d97b9999b45267d727"), "f1" : "A" } /* 3 */ { "_id" : ObjectId("564d88d97b9999b45267d723"), "f1" : "A", "f2" : "A" } /* 4 */ { "_id" : ObjectId("564d88d97b9999b45267d725"), "f1" : "A", "f2" : "B" } /* 5 */ { "_id" : ObjectId("564d88d97b9999b45267d724"), "f1" : "A", "f2" : "a" } /* 6 */ { "_id" : ObjectId("564d88d97b9999b45267d726"), "f1" : "A", "f2" : "b" } /* 7 */ { "_id" : ObjectId("564d88d97b9999b45267d731"), "f1" : "B" } /* 8 */ { "_id" : ObjectId("564d88d97b9999b45267d72d"), "f1" : "B", "f2" : "A" } /* 9 */ { "_id" : ObjectId("564d88d97b9999b45267d72f"), "f1" : "B", "f2" : "B" } /* 10 */ { "_id" : ObjectId("564d88d97b9999b45267d72e"), "f1" : "B", "f2" : "a" } /* 11 */ { "_id" : ObjectId("564d88d97b9999b45267d730"), "f1" : "B", "f2" : "b" } /* 12 */ { "_id" : ObjectId("564d88d97b9999b45267d72c"), "f1" : "a" } /* 13 */ { "_id" : ObjectId("564d88d97b9999b45267d728"), "f1" : "a", "f2" : "A" } /* 14 */ { "_id" : ObjectId("564d88d97b9999b45267d72a"), "f1" : "a", "f2" : "B" } /* 15 */ { "_id" : ObjectId("564d88d97b9999b45267d729"), "f1" : "a", "f2" : "a" } /* 16 */ { "_id" : ObjectId("564d88d97b9999b45267d72b"), "f1" : "a", "f2" : "b" } /* 17 */ { "_id" : ObjectId("564d88d97b9999b45267d736"), "f1" : "b" } /* 18 */ { "_id" : ObjectId("564d88d97b9999b45267d732"), "f1" : "b", "f2" : "A" } /* 19 */ { "_id" : ObjectId("564d88d97b9999b45267d734"), "f1" : "b", "f2" : "B" } /* 20 */ { "_id" : ObjectId("564d88d97b9999b45267d733"), "f1" : "b", "f2" : "a" } /* 21 */ { "_id" : ObjectId("564d88d97b9999b45267d735"), "f1" : "b", "f2" : "b" } /* 22 */ { "_id" : ObjectId("564d88d97b9999b45267d6e3"), "f0" : "A" } /* 23 */ { "_id" : ObjectId("564d88d97b9999b45267d6d3"), "f0" : "A", "f1" : "A" } /* 24 */ { "_id" : ObjectId("564d88d97b9999b45267d6cf"), "f0" : "A", "f1" : "A", "f2" : "A" } /* 25 */ { "_id" : ObjectId("564d88d97b9999b45267d6d1"), "f0" : "A", "f1" : "A", "f2" : "B" } /* 26 */ { "_id" : ObjectId("564d88d97b9999b45267d6d0"), "f0" : "A", "f1" : "A", "f2" : "a" } /* 27 */ { "_id" : ObjectId("564d88d97b9999b45267d6d2"), "f0" : "A", "f1" : "A", "f2" : "b" } /* 28 */ { "_id" : ObjectId("564d88d97b9999b45267d6dd"), "f0" : "A", "f1" : "B" } /* 29 */ { "_id" : ObjectId("564d88d97b9999b45267d6d9"), "f0" : "A", "f1" : "B", "f2" : "A" } /* 30 */ { "_id" : ObjectId("564d88d97b9999b45267d6db"), "f0" : "A", "f1" : "B", "f2" : "B" } /* 31 */ { "_id" : ObjectId("564d88d97b9999b45267d6da"), "f0" : "A", "f1" : "B", "f2" : "a" } /* 32 */ { "_id" : ObjectId("564d88d97b9999b45267d6dc"), "f0" : "A", "f1" : "B", "f2" : "b" } /* 33 */ { "_id" : ObjectId("564d88d97b9999b45267d6d8"), "f0" : "A", "f1" : "a" }
  • Options with sorting after data extraction you do not consider? Why? - Dmitriy Simushev
  • This is not suitable, as there may be a large number of documents. And I would like to use a database index. - Alexey

1 answer 1

Indexes in MongoDB cannot yet be case insensitive. Here is the feature request with the status "open":

https://jira.mongodb.org/browse/SERVER-90

If optimal use of indexes is not critical for you, you can solve the problem with the Aggregation Framework :

Decision

 db.test.aggregate( { "$project": { f0: 1, f1: 1, f2: 1, f0_sortable: { $cond: { if: { $gt: [ "$f0", null ] }, then: { "$toLower": "$f0" }, else: [] } }, f1_sortable: { $cond: { if: { $gt: [ "$f1", null ] }, then: { "$toLower": "$f1" }, else: [] } }, f2_sortable: { $cond: { if: { $gt: [ "$f2", null ] }, then: { "$toLower": "$f2" }, else: [] } } } }, { "$sort": { f0_sortable: 1, f1_sortable: 1, f2_sortable: 1, f0: 1, f1: 1, f2: 1 } }, { "$project": { f0: 1, f1: 1, f2: 1 } })

Explanation

1. First $project

At this step, we select the initial fields ( f0 , f1 , f2 ) for each object and create new fields ( *_sortable ) that will be used only for sorting. For example:

 $cond: { if: { $gt: [ "$f2", null ] }, then: { "$toLower": "$f2" }, else: [] }

This expression will return the value of the f2 field in lowercase if the object has a non-zero field f2 . Otherwise, it will return [] - i.e. array I did this because arrays are always considered in the sort order more than, strings.

2. $sort

Sort first on our *_sortable fields, and then also on the original fields, for beauty ( A, a, a will go before a, A, A ).

3. Second $project

We leave in the results only the initial fields f0 , f1 , f2 .