mongodb – order by the sum of size of multiple arrays

I have a collection with documents structured like this:

{
    "company": "abc",
    "countries": {
        "australia": ["banana", "kiwi", "avocado"],
        "germany": ["apple", "cucumber"],
        "columbia": ["banana"]
    }
}

Question:

How can i find all documents sorted by the sum of the length of all arrays in "countries" ?

In the provided example, the sum would be 6.

The keys in countries may only contain a subset, so in in this example it is australia, germany and columbia, but in another document it may just be australia or germany and columbia.

Thanks for your help!

>Solution :

One option is to use $reduce to calculate this value for each document:

db.collection.aggregate([
  {$set: {
      count: {$reduce: {
          input: {$objectToArray: "$countries"},
          initialValue: 0,
          in: {$add: ["$$value", {$size: "$$this.v"}]}
      }}
  }},
  {$sort: {count: -1}},
  {$unset: 'count'}
])

See how it works on the playground example