How to get key that has max value inside a nested object in MongoDB collection?

user_14942451 0
Viewed 175

Consider the below MongoDB collection

[
    {
        _id: 123,
        links: {
            "google.com": 3,
            "facebook.com": 4,
            "whatsapp.com": 6
        }
    },
    {
        _id: 123,
        links: {
            "google.com": 4,
            "facebook.com": 6,
            "yahoo.com": 8
        }
    },
    {
        _id: 123,
        links: {
            "mail.com": 3,
            "twitter.com": 4,
            "whatsapp.com": 5
        }
    }
]

Let's say the above documents are in the mongodb collection and I want to retrive the max link that has the more number.
For example:

  • "google.com" has 7 as sum
  • "facebook.com" has 10 as sum
  • "whatsapp.com" has 11 as sum
  • "yahoo.com" has 10
  • "mail.com" has 3
  • "twitter.com" has 4

So here the highest number is for key "whatsapp.com" I need this key and total sum 11
Thanks in advance

aggregation-framework mongodb-query mongodb javascript
1 Answers
  • $objectToArray convert links object to key-value format array of object
  • $unwind to deconstruct the links array
  • $group by links name and count total value
  • $sort by total value in descending order
  • $limit 1 to select single document
db.collection.aggregate([
  { $addFields: { links: { $objectToArray: "$links" } } },
  { $unwind: "$links" },
  {
    $group: {
      _id: "$links.k",
      count: { $sum: "$links.v" }
    }
  },
  { $sort: { count: -1 } },
  { $limit: 1 }
])

Playground

user_89871280
Related