Mongodb sort inner array

Question

I've been looking for a while now and can't seem to sort an inner array and keep that in the doc that I'm currently working with.

{
    "service": {
        "apps": {
            "updates": [
              {
                "n" : 1
                "date": ISODate("2012-03-10T16:15:00Z")
              },
              {
                "n" : 2
                "date": ISODate("2012-01-10T16:15:00Z")
              },
              {
                "n" : 5
                "date": ISODate("2012-07-10T16:15:00Z")
              }
            ]
        }
     }
 }

So I want to keep the item to be returned as the service, but have my updates array sorted. So far with the shell I have:

db.servers.aggregate(
        {$unwind:'$service'},
        {$project:{'service.apps':1}},
        {$unwind:'$service.apps'}, 
        {$project: {'service.apps.updates':1}}, 
        {$sort:{'service.apps.updates.date':1}});

Anyone think they can help on this?

score 61 · Accepted Answer · answered Mar 13 '13 at 15:09

61

You can do this by $unwinding the updates array, sorting the resulting docs by date, and then $grouping them back together on _id using the sorted order.

db.servers.aggregate(
    {$unwind: '$service.apps.updates'}, 
    {$sort: {'service.apps.updates.date': 1}}, 
    {$group: {_id: '$_id', 'updates': {$push: '$service.apps.updates'}}}, 
    {$project: {'service.apps.updates': '$updates'}})

answered Mar 13 '13 at 15:09

JohnnyHK

270,398
59
567
434

Awesome! I'm pretty new to aggregation and had a feeling it could do something like this. – Ricky Hartmann Mar 13 '13 at 15:26
1

what about if apps had a field like "name" and I wanted to keep the name in the result set as well? – Ricky Hartmann Mar 13 '13 at 15:57
@user1251624 You would include that field in the `$group` (in the `_id` or as a separate field) and `$project`. If you need more help on that it's probably best to ask that as a separate question as it can be non-trivial. – JohnnyHK Mar 13 '13 at 16:06
1

@JohnnyHK Is the order of `group`ing after `sort` always guaranteed? – RaR May 22 '17 at 13:27
1

@RaR For `$push` yes, AFAIK. For `$addToSet` no, unfortunately. – Ingo Bürk Jun 17 '17 at 22:45
What if "apps" was also an array? I have this problem. It returned 0 results – nixsix6 Dec 14 '17 at 08:44
It is sorting the full set the documents before grouping, not efficient if you have so much data – dhalfageme Aug 12 '20 at 10:33

Xavier Guihot · Answer 2 · 2020-03-31T18:23:23.890

Starting in Mongo 4.4, the $function aggregation operator allows applying a custom javascript function to implement behaviour not supported by the MongoDB Query Language.

For instance, in order to sort an array of objects by one of their fields:

// {
//   "service" : { "apps" : { "updates" : [
//     { "n" : 1, "date" : ISODate("2012-03-10T16:15:00Z") },
//     { "n" : 2, "date" : ISODate("2012-01-10T16:15:00Z") },
//     { "n" : 5, "date" : ISODate("2012-07-10T16:15:00Z") }
//   ]}}
// }
db.collection.aggregate(
  { $set: {
    { "service.apps.updates":
      { $function: {
          body: function(updates) {
            updates.sort((a, b) => a.date - b.date);
            return updates;
          },
          args: ["$service.apps.updates"],
          lang: "js"
      }}
    }
  }
)
// {
//   "service" : { "apps" : { "updates" : [
//     { "n" : 2, "date" : ISODate("2012-01-10T16:15:00Z") },
//     { "n" : 1, "date" : ISODate("2012-03-10T16:15:00Z") },
//     { "n" : 5, "date" : ISODate("2012-07-10T16:15:00Z") }
//   ]}}
// }

This modifies the array in place, without having to apply a combination of expensive $unwind, $sort and $group stages.

$function takes 3 parameters:

body, which is the function to apply, whose parameter is the array to modify.
args, which contains the fields from the record that the body function takes as parameter. In our case "$service.apps.updates".
lang, which is the language in which the body function is written. Only js is currently available.

For Atlas users, `$function` is not available in free tier (as of May 2021) — saibbyweb, May 11 '21 at 09:43

Mongodb sort inner array

2 Answers2

Linked

Related