How to sort and find three largest elements in array?

Question

I want generate report from mongodb. I have a aggregation which generated:

[{"_id": {
      "m": 1,
      "y": 2020
    },
    "meals": [
      {
        "name": "Sandwich",
        "servings": 2
      },
      {
        "name": "Fish",
        "servings": 7
      },
      {
        "name": "Pizza",
        "servings": 3
      },
      {
        "name": "Beef",
        "servings": 3
      },
      {
        "name": "Soup",
        "servings": 3
      }]},
  {"_id": {
      "m": 12,
      "y": 2018
    },
    "meals": [
      {
        "name": "Beef",
        "servings": 1
      },
      {
        "name": "Spaghetti",
        "servings": 2
      }]}]

I need to get 3 elements where servings the largest and sort them. If elements are not three, just sort. For example:

[{"_id": {
          "m": 1,
          "y": 2020
        },
        "meals": [
          {
            "name": "Fish",
            "servings": 7
          },
          {
            "name": "Pizza",
            "servings": 3
          },
          {
            "name": "Beef",
            "servings": 3
          }]},
      {"_id": {
          "m": 12,
          "y": 2018
        },
        "meals": [
          {
            "name": "Spaghetti",
            "servings": 2
          },
          {
            "name": "Beef",
            "servings": 1
          }
        ]}]

I can't use find(), because I want to do this in aggregation. I tried to use $filter, but I am doing something wrong.

can you provide the code you tried to get the solution with $filter? — Michael Parkadze, Jun 24 '21 at 08:56
I think this will help or even answer your question: https://stackoverflow.com/questions/15388127/mongodb-sort-inner-array — Michael Parkadze, Jun 24 '21 at 09:00

score 1 · Accepted Answer · answered Jun 24 '21 at 09:02

$unwind deconstruct meals array
$sort by servings in descending order
$group by _id and reconstruct meals array
$slice to get first 3 elements from meals

db.collection.aggregate([
  { $unwind: "$meals" },
  { $sort: { "meals.servings": -1 } },
  {
    $group: {
      _id: "$_id",
      meals: { $push: "$meals" }
    }
  },
  {
    $project: {
      meals: { $slice: ["$meals", 3] }
    }
  }
])

Playground

score 0 · Answer 2 · 2021-06-24T09:13:30.120

0

It is very important to sort and find data from the servers by mongodb. So I think that you should use the some commands as cooperating. I mean in your case, you use that

    find().sort().limit(3)

I hope that it would help you.

edited Jun 24 '21 at 09:13

answered Jun 24 '21 at 09:01

Thanks for your help, but I could not use find() since the sort and slice are in the aggregation() – Vlados Jun 24 '21 at 09:17

score 0 · Answer 3 · answered Jun 24 '21 at 09:17

0

$unwind and $group are expensive.

Starting form version >= 4.4, MongoDB supports functions. You can use it in combination with slice to get the desired result.

db.collection.aggregate([
  {
    "$project": {
      "meals": {
        "$slice": [
          {
            "$function": {
              "body": "function(updates) { updates.sort((a, b) => b.servings - a.servings); return updates;}",
              "args": [
                "$meals"
              ],
              "lang": "js"
            }
          },
          3
        ],
      },
    },
  },
])

Mongo Playground Sample Execution

answered Jun 24 '21 at 09:17

hhharsha36

3,089
2
12
12

actually `$function` is expensive because it is executing javascript, they have a note in [documentation](https://docs.mongodb.com/manual/reference/operator/aggregation/function/) **Only use the $function operator if the provided pipeline operators cannot fulfill your application's needs.** – turivishal Jun 24 '21 at 13:45
Thanks for the info @turivishal, yes I have gone through that documentation, but the `$unwind` and `$group` stage will take even more time if the number of array elements and the total number of documents on a collection is very high (Example: 1,000,000 records with 10 array elements each) right? So, I taught a simple `$function` on the `$projection` stage won't take much time since its complexity should be just `O(n2)` [O of n square]. Please correct me if I am wrong. – hhharsha36 Jun 24 '21 at 14:18
$unwind is expensive but, when we use it with limit, it will handle your work in average time. The $function uses a different language environment, when you have 1000 documents it will initialize that lang environment and execute that code 1000 time, JS is a different territory than mongodb current native operators. – turivishal Jun 24 '21 at 14:44
@turivishal I see. Thanks for taking the time to reply. Will keep this in mind from now onwards. – hhharsha36 Jun 24 '21 at 14:47

How to sort and find three largest elements in array?

3 Answers3