Reputation: 39437
MongoDB Type Order
I have this collection in MongoDB. It contains
values of different types under the val key.
Also, note that I am sorting it by val ascending.
[test] 2014-02-20 08:53:11.857 >>> db.account.find().sort({val:1});
{
"_id" : ObjectId("5304d25786dd4b348bcc2b2e"),
"username" : "usr10",
"password" : "123",
"val" : [ ]
}
{
"_id" : ObjectId("5304d29986dd4b348bcc2b2f"),
"username" : "usr20",
"password" : "456",
"val" : null
}
{
"_id" : ObjectId("5304e31686dd4b348bcc2b37"),
"username" : "usr80",
"password" : "555",
"val" : 1
}
{
"_id" : ObjectId("5304d50a86dd4b348bcc2b32"),
"username" : "usr50",
"password" : "555",
"val" : [
40
]
}
{
"_id" : ObjectId("5304d4c886dd4b348bcc2b31"),
"username" : "usr40",
"password" : "777",
"val" : 200
}
{
"_id" : ObjectId("5304d2a186dd4b348bcc2b30"),
"username" : "usr30",
"password" : "888",
"val" : {
}
}
{
"_id" : ObjectId("5304d97786dd4b348bcc2b33"),
"username" : "usr50",
"password" : "555",
"val" : {
"ok" : 1
}
}
{
"_id" : ObjectId("5304e2dc86dd4b348bcc2b36"),
"username" : "usr80",
"password" : "555",
"val" : true
}
{
"_id" : ObjectId("5304e22f86dd4b348bcc2b34"),
"username" : "usr60",
"password" : "555",
"val" : ISODate("2014-02-19T16:56:15.787Z")
}
{
"_id" : ObjectId("5304e2c786dd4b348bcc2b35"),
"username" : "usr70",
"password" : "555",
"val" : /abc/
}
[test] 2014-02-20 08:53:19.357 >>>
I am reading a book which says the following.
MongoDB has a hierarchy as to how types compare. Sometimes you will have
a single key with multiple types: for instance, integers and booleans, or strings
and nulls. If you do a sort on a key with a mix of types, there is a predefined
order that they will be sorted in. From least to greatest value, this ordering
is as follows:
1. Minimum value
2. null
3. Numbers (integers, longs, doubles)
4. Strings
5. Object/document
6. Array
7. Binary data
8. Object ID
9. Boolean
10. Date
11. Timestamp
12. Regular expression
13. Maximum value
So why is my sorting order different? For example,
when I sort (see above) I see these strange things:
1) I have no idea what 'minimum value' and 'maximum value' mean.
2) An array comes before a number. And an empty
array comes even before null.
3) The number 1 comes before an array
4) The array [40] comes between numbers 1 and 200.
Could someone just explain this result in some details?
Many thanks in advance.
Upvotes: 6
Views: 943
Answers (4)
Reputation: 482
MongoDB type comparison
Why
MongoDB is a schemaless database, which allows you to store pieces of information (document) without defining a structure (schema of fields), like in SQL where we have to define a schema in the form of columns and their data types.
In the case of sorting or data retrival this may be problematic. In order to be predictable, MongoDB has a fixed type order to sort documents of different types as you already mentioned:
- Minimum value (MinKey)
- Null
- Numbers (ints, longs, doubles, decimals)
- Strings
- Object/document
- Array
- Binary data
- ObjectId
- Boolean
- Date
- Timestamp
- Regex
- Maximum value (MaxKey)
In the process of sorting values get compared, in order to decide how they should be positioned. This list defines from lowest to highest what should happen when theses data types get compared.
{ "a": 0 }
{ "a": "a" }
{ "a": 1 }
When sorted by a ascending numbers precide strings, as the list states Strings (4) and Numbers (3).
Conversions
For certain data types MongoDB tries to convert them when compared (If you know JavaScript this should feel familiar).
[] -> null
[[]] -> Array
// but
"" -> String
0 -> Number
...
For one dimensional Arrays, their conversions depend on the order of sorting.
// ascending
[1, 2, 3, 4, 5] (Array) -> 1 (Int)
[5, 8, 10] (Array) -> 5 (Int)
// descending
[1, 2, 3, 4, 5] (Array) -> 5 (Int)
[5, 8, 10] (Array) -> 10 (Int)
MinKey / MaxKey
MinKey is the lowest possible value in every comparison, MaxKey the highest. Both are used internally, as they are always at the beginning or end of a collection when sorted.
Questions
- I have no idea what 'minimum value' and 'maximum value' mean.
These are MinKey and MaxKey. See above.
- An array comes before a number. And an empty array comes even before null.
It does not. As already explained an [] is always converted to null. When comparing equal values like, null and null or 40 and [40], the documents are sorted by natural order, which is in your case the timestamp in the ObjectId. Try creating first the null entry and then the empty array.
- The number 1 comes before an array
[40] is not an Array since it's converted to a Number. [[40]] would be an Array.
- The array [40] comes between numbers 1 and 200.
If think you've got it. See 3.
Sources and further reading
- https://www.w3resource.com/mongodb/mongodb-data-types.php
- https://docs.mongodb.com/manual/reference/bson-type-comparison-order/
- https://docs.mongodb.com/manual/reference/bson-types/
Upvotes: 0
Reputation: 311855
The type order is only used when there isn't another supported way of ordering elements. Array fields have their own sorting behavior where the minimum value of their elements are used on an ascending sort, and the maximum value on a descending sort. The type of that minimum or maximum value is then used to order the docs with fields of that type.
So [40] comes after 1, but before 200 because the minimum value of that array is 40.
The empty array has no value at all, which is why it ends up with the doc where the value is null. If I reverse the sort, they stay in the same order which implies that MongoDB considers them equal.
Upvotes: 2
Reputation: 57192
Where is the sort clause in your query? Your sort order appears to be the default order - notice the ascending ObjectIds. You mentioned you're sorting by val so I would expect your query to be
db.account.find({val:1})
Upvotes: 0
Reputation: 19464
Your book says the same as the official documentation. But this also does not explain the obscure sorting order of the two arrays. At least the two types Minimum value and Maximum value are explained. They are internal.
Upvotes: 2