Inconsistency with $group and $sort

Working with Data
, ,
#1

Let’s say I have the following data set:

test> db.test.insertMany([{_id: 'a', v: [2]}, {_id: 'b', v: [1, 3]}])
{ acknowledged: true, insertedIds: { '0': 'a', '1': 'b' } }

A regular query with sorting works as expected:

test> db.test.find().sort({v: 1})
[ { _id: 'b', v: [ 1, 3 ] }, { _id: 'a', v: [ 2 ] } ]

test> db.test.find().sort({v: -1})
[ { _id: 'b', v: [ 1, 3 ] }, { _id: 'a', v: [ 2 ] } ]

An aggregation pipeline with $sort works the same way:

test> db.test.aggregate([{$sort: {v: 1}}])
[ { _id: 'b', v: [ 1, 3 ] }, { _id: 'a', v: [ 2 ] } ]

test> db.test.aggregate([{$sort: {v: -1}}])
[ { _id: 'b', v: [ 1, 3 ] }, { _id: 'a', v: [ 2 ] } ]

But when used with $group by the same array field v, sorting works differently:

test> db.test.aggregate([{$group: {_id: "$v"}}, {$sort: {_id: 1}}])
[ { _id: [ 1, 3 ] }, { _id: [ 2 ] } ]

test> db.test.aggregate([{$group: {_id: "$v"}}, {$sort: {_id: -1}}])
[ { _id: [ 2 ] }, { _id: [ 1, 3 ] } ]

Note that the last result is different.

I could not find anything in the documentation that would explain that behavior. Please help me understand it.

#3

Hello @Ratchet ,

Welcome to The MongoDB Community Forums! :wave:

There is a difference in behaviour on how $sort works while doing a $group operation on an array.

I believe why you were getting [1,3] every time before [2] in above examples was because both the positions of arrays were being checked when a normal sort operation was underway but with $group [1,3] is considered as distinct value and when $sort is done with group, it will check 1 is less than 2 hence it will provide the result on just this condition, it will not check the second position that is 3 is less than 2.

Lastly, I do not recommend sorting on an array field as the output can be unintuitive, which you saw when an ascending and descending sort resulted in the same ordering. Instead I would recommend having a more definite query field (proper sort key) which should not create ambiguity in the query planner.

Regards,
Tarun