How to sort Multi-keys MongoDB?

Question

In MongoDB, a field can have multiple values (an array of values). Each of them is indexed, so you can filter any value. But can you also “arrange” a field with multiple values ​​and what is the result?

Update:

> db.test.find().sort({a:1}) { "_id" : ObjectId("4f27e36b5eaa9ebfda3c1c53"), "a" : [ 0 ] } { "_id" : ObjectId("4f27e3845eaa9ebfda3c1c54"), "a" : [ 0, 1 ] } { "_id" : ObjectId("4f27df6e5eaa9ebfda3c1c4c"), "a" : [ 1, 1, 1 ] } { "_id" : ObjectId("4f27df735eaa9ebfda3c1c4d"), "a" : [ 1, 1, 2 ] } { "_id" : ObjectId("4f27df795eaa9ebfda3c1c4e"), "a" : [ 2, 1, 2 ] } { "_id" : ObjectId("4f27df7f5eaa9ebfda3c1c4f"), "a" : [ 2, 2, 1 ] } { "_id" : ObjectId("4f27df845eaa9ebfda3c1c50"), "a" : [ 2, 1 ] } { "_id" : ObjectId("4f27e39a5eaa9ebfda3c1c55"), "a" : [ 2 ] } 

When using unequal arrays of length, a longer array is “lower” than a shorter array

So why [0] before [0,1], but [2] after [2,1]? Perhaps sorting is done only for the first element of the array? Or the lowest? And after that is the insertion order?

Also, how is this implemented in the case of index scanning (as opposed to table scanning)?

Answer 1

Sorting array elements is quite complicated. Since the elements of the array are indexed separately, sorting by array field will actually lead to some interesting situations. What happens is that MongoDB will sort them based on the lowest or largest value in the array (depending on the direction of sorting). In addition, the order is natural.

This leads to things like:

 > db.test.save({a:[1]}) > db.test.save({a:[0,2]}) > db.test.find().sort({a:1}) { "_id" : ObjectId("4f29026f5b6b8b5fa49df1c3"), "a" : [ 0, 2 ] } { "_id" : ObjectId("4f2902695b6b8b5fa49df1c2"), "a" : [ 1 ] } > db.test.find().sort({a:-1}) { "_id" : ObjectId("4f29026f5b6b8b5fa49df1c3"), "a" : [ 0, 2 ] } { "_id" : ObjectId("4f2902695b6b8b5fa49df1c2"), "a" : [ 1 ] } 

In other words. The same order for reverse sorts. This is because the “a” field of the top document contains both the minimum and highest values.

So effective for sorting MongoDB ignores all values ​​in the array that are not either the highest ({field: -1} sort) or the lowest ({field: 1} sort) and orders the remaining values.

To draw a (simplified) picture, it works something like this:

smoothed b-tree for index {a: 1}, above sample documents:

 "a" value 0 -> document 4f29026f5b6b8b5fa49df1c3 "a" value 1 -> document 4f2902695b6b8b5fa49df1c2 "a" value 2 -> document 4f29026f5b6b8b5fa49df1c3 

As you can see, scanning from top to bottom and from bottom to top will result in the same order.

Empty arrays are the "minimum" possible value of the array and, thus, will be displayed at the top and bottom of the above queries, respectively.

Indexes do not change the sort behavior on arrays.