How to sort multiple array independently in one mongo document using aggregation

Question

After some aggregations I have only one document like this:

{
  a:[3,5,8,1,...],
  b:[5,2,6,5,...],
  c:[2,6,3,1,...]
}

What is the best way to get:

{
  a:[1,2,3,4,...],
  b:[1,2,3,4,...],
  c:[1,2,3,4,...]
}

I need it to be sorted to do other aggregations so I can't use find or update.

score 2 · Accepted Answer · answered Oct 27 '20 at 20:24

The closest working solution I've got is the following aggregation:

[
  {
    $facet: {
      a: [
        { $unwind: '$a' },
        { $sort: { a: 1 } },
        {
          $group: {
            _id: '$_id',
            a: { $push: '$a' }
          }
        }
      ],
      b: [
        { $unwind: '$b' },
        { $sort: { b: 1 } },
        {
          $group: {
            _id: '$_id',
            b: { $push: '$b' }
          }
        }
      ],
      c: [
        { $unwind: '$c' },
        { $sort: { c: 1 } },
        {
          $group: {
            _id: '$_id',
            c: { $push: '$c' }
          }
        }
      ]
    }
  },
  {
    $set: {
      a: { $arrayElemAt: ['$a.a', 0] },
      b: { $arrayElemAt: ['$b.b', 0] },
      c: { $arrayElemAt: ['$c.c', 0] }
    }
  }
]

In short, what this does is: $facet performs identical operations on each a, b, c field in parallel; each field is split with $unwind, sorted in ascending order and then grouped on itself.

Until this point we already have sorted fields but each field is now an object with appropriate name (for example, { _id: '', a: [...] }). So, to return to the array we had earlier, another stage is to simply extract only the needed field from each object. This is done with $set and $arrayElemAt (which takes first array field). So, in the end we have each field sorted in ascending order while having the same amount of documents.

Note: this only works if you have a single result document. If you have multiple of them, then you will need to slightly change stages:

$facet stage remains the same
Updated $set (second) stage is changed to:

Use $unwind + updated $set stages for each separate field (in this case, we have a, b, c):

{ $unwind: '$a' },
{ $unwind: '$b' },
{ $unwind: '$c' },
{ $set: { a: '$a.a' } },
{ $set: { b: '$b.b' } },
{ $set: { c: '$c.c' } }

This might not be the prettiest approach but I could not find shorter version.

Thank you I didn't know about the `$facet` operator, I was doing the same but not in parallel so it's really helpful ! — Vee, Oct 27 '20 at 20:51
@Vee Yeah, it’s still relative new thing for me as well but I have already found a few cases where it’s super useful :) — Gynteniuxas, Oct 27 '20 at 22:53

score 1 · Answer 2 · answered Oct 27 '20 at 21:52

Starting at version 4.4, you can use $function and sort your arrays directly with javascript.

db.collection.aggregate([
  {
    $set: {
      "a": {
        $function: {
          body: "function(a){a.sort((x, y)=>x-y);return a;}",
          args: [
            "$a"
          ],
          lang: "js"
        }
      },
      "b": {
        $function: {
          body: "function(b){b.sort((x, y)=>x-y);return b;}",
          args: [
            "$b"
          ],
          lang: "js"
        }
      },
      "c": {
        $function: {
          body: "function(c){c.sort((x, y)=>x-y);return c;}",
          args: [
            "$c"
          ],
          lang: "js"
        }
      }
    }
  }
])

You can test it here.

First time hearing about this but it’s quite interesting, looks very useful (although I wonder if it’s a fast operator) — Gynteniuxas, Oct 27 '20 at 22:57

How to sort multiple array independently in one mongo document using aggregation

2 Answers2