MongoDB - Safely sort inner array after group

Question

I'm trying to look up all records that match a certain condition, in this case _id being certain values, and then return only the top 2 results, sorted by the name field.

This is what I have

db.getCollection('col1').aggregate([
    {$match: {fk: {$in: [1, 2]}}},
    {$sort: {fk: 1, name: -1}},
    {$group: {_id: "$fk", items: {$push: "$$ROOT"} }},
    {$project: {items: {$slice: ["$items", 2]} }}
])

and it works, BUT, it's not guaranteed. According to this Mongo thread $group does not guarantee document order.

This would also mean that all of the suggested solutions here and elsewhere, which recommend using $unwind, followed by $sort, and then $group, would also not work, for the same reason.

What is the best way to accomplish this with Mongo (any version)? I've seen suggestions that this could be accomplished in the $project phase, but I'm not quite sure how.

Answer 1

You are correct in saying that the result of $group is never sorted.

$group does not order its output documents.

Hence doing a;

{$sort: {fk: 1}}

then grouping with

{$group: {_id: "$fk", ... }}, 

will be a wasted effort.

---

But there is a silver lining with sorting before $group stage with name: -1. Since you are using $push (not an $addToSet), inserted objects will retain the order they've had in the newly created items array in the $group result. You can see this behaviour here (copy of your pipeline)

The items array will always have;

"items": [
  {
    ..
    "name": "Michael"
  },
  {
    ..
    "name": "George"
  }
]

in same order, therefore your nested array sort is a non-issue! Though I am unable to find an exact quote in documentation to confirm this behaviour, you can check;

• this,
• or this where it is confirmed.
• Also, accumulator operator list for $group, where $addToSet has "Order of the array elements is undefined." in its description, whereas the similar operator $push does not, which might be an indirect evidence? :)

Just a simple modification of your pipeline where you move the fk: 1 sort from pre-$group stage to post-$group stage;

db.getCollection('col1').aggregate([
    {$match: {fk: {$in: [1, 2]}}},
    {$sort: {name: -1}},
    {$group: {_id: "$fk", items: {$push: "$$ROOT"} }},
    {$sort: {_id: 1}},
    {$project: {items: {$slice: ["$items", 2]} }}
])

should be sufficient to have the main result array order fixed as well. Check it on mongoplayground

Answer 2

$group doesn't guarantee the document order but it would keep the grouped documents in the sorted order for each bucket. So in your case even though the documents after $group stage are not sorted by fk but each group (items) would be sorted by name descending. If you would like to keep the documents sorted by fk you could just add the {$sort:{fk:1}} after $group stage

You could also sort by order of values passed in your match query should you need by adding a extra field for each document. Something like

db.getCollection('col1').aggregate([
    {$match: {fk: {$in: [1, 2]}}},
    {$addField:{ifk:{$indexOfArray:[[1, 2],"$fk"]}}},
    {$sort: {ifk: 1, name: -1}},
    {$group: {_id: "$ifk", items: {$push: "$$ROOT"}}},
    {$sort: {_id : 1}},
    {$project: {items: {$slice: ["$items", 2]}}}
])

Update to allow array sort without group operator : I've found the jira which is going to allow sort array.

You could try below $project stage to sort the array.There maybe various way to do it. This should sort names descending.Working but a slower solution.

{"$project":{"items":{"$reduce":{
  "input":"$items",
  "initialValue":[],
  "in":{"$let":{
    "vars":{"othis":"$$this","ovalue":"$$value"},
    "in":{"$let":{
      "vars":{
        //return index as 0 when comparing the first value with initial value (empty) or else return the index of value from the accumlator array which is closest and less than the current value.
        "index":{"$cond":{
          "if":{"$eq":["$$ovalue",[]]},
          "then":0,
          "else":{"$reduce":{
            "input":"$$ovalue",
            "initialValue":0,
            "in":{"$cond":{
              "if":{"$lt":["$$othis.name","$$this.name"]},
              "then":{"$add":["$$value",1]},
              "else":"$$value"}}}}
        }}
      },
      //insert the current value at the found index
      "in":{"$concatArrays":[
          {"$slice":["$$ovalue","$$index"]},
          ["$$othis"],
          {"$slice":["$$ovalue",{"$subtract":["$$index",{"$size":"$$ovalue"}]}]}]}
    }}}}
}}}}

Simple example with demonstration how each iteration works

db.b.insert({"items":[2,5,4,7,6,3]});

othis   ovalue      index      concat arrays (parts with counts)       return value
2       []          0           [],0            [2]     [],0           [2]
5       [2]         0           [],0            [5]     [2],-1         [5,2]          
4       [5,2]       1           [5],1           [4]     [2],-1         [5,4,2]
7       [5,4,2]     0           [],0            [7]     [5,4,2],-3     [7,5,4,2]
6       [7,5,4,2]   1           [7],1           [6]     [5,4,2],-3     [7,6,5,4,2]
3       [7,6,5,4,2] 4           [7,6,5,4],4     [3]     [2],-1         [7,6,5,4,3,2]

Reference - Sorting Array with JavaScript reduce function