Categories

JavaScriptPHPHTMLCssPythonWordPressWeb developmentLinuxMySQLAndroidWindowsJavaC++ / C#LayoutReactUbuntuYiiDjangoProgramming1C-BitrixLaravelSystem administrationTelegramJSONDebianPostgreSQLIOSGoogleHtaccessWindows ServerGitMacOSGoogle ChromeCMSWooСommerceBootstrapNginxSQLUnityAPI
Make a leaderReport
Q
Q
Qvini2015-11-19 11:47:32
MongoDB
Qvini, 2015-11-19 11:47:32

How to lower the priority of empty fields in MongoDB when sorting?

Good afternoon. I have a problem with sorting. I need to sort by the fields f0, f1, f2, but do not take into account the case of the character, and put records that do not have a field at the end of the list.
Here is 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});

And here is the output, and it shows that it pulls empty values ​​to the top, then capital letters go, and then small ones. 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"
}

Thanks

Reply
Answer the question

Answer the question

In order to leave comments, you need to log in

1 answer(s)
Report
L
lega, 2015-11-19
@lega

Make a separate field under the index, for example:

{f0: "A", f1: "a", f2: "B"} -> _index: "aab"
{ f1: "A", f2: "b"} -> _index: "xab"
"x" instead of a missing field, something used to go to the end of the sort
If uniqueness is needed, then this "key" can be written in the _id field.
As a result, it will work faster and consume less memory.

Similar questions
K
khooni172018-05-02 00:12:05
What analogues does mlab have for mongodb hosting? 0Reply
P
P_Alexander2018-05-04 10:47:45
How to bring from Document to the object I need? 1Reply
P
PAJCH2015-12-28 14:45:55
What is the best way to implement the mongodb structure? 2Reply
A
Andrey Mollinzz2018-05-08 21:45:10
Why is the value not returned? 2Reply
N
Nurasyl Aldan2021-08-23 16:36:59
How to query like select ... for update in MongoDB? 1Reply

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question