Finding max value in an array of sub-documents

3,427 views
Skip to first unread message

Alan Niederauer

unread,
Jul 15, 2017, 5:48:15 PM7/15/17
to mongodb-user
I have a document that has an array of sub-documents where the key is an integer and the value is a double, such as:

document {
   description
   values [
      {key, value},
      {key, value}
   ]
}

I want to return the sub-document from the values array that has the largest value, For instance, if the following sub-document exists:

   values[{key: 1, value: 5}, {key: 5, value: 4}, {key: 3, value: 10}]

I wan to return the sub-document of {key: 5, value:4}. And actually, I really only need to return "value'. How would I go about doing this?

Kevin Adistambha

unread,
Jul 25, 2017, 10:23:17 PM7/25/17
to mongodb-user

Hi Alan

I think you gave an incorrect example. I believe you wanted to return the sub-document whose “key” is the largest?

You can use the aggregation framework to do this. Using your example:

> db.test.find()
{
  "_id": ObjectId("5977f8762dbe1d773beb11d0"),
  "values": [
    {
      "key": 1,
      "value": 5
    },
    {
      "key": 5,
      "value": 4
    },
    {
      "key": 3,
      "value": 10
    }
  ]
}

> db.test.aggregate([
    {$unwind: '$values'},        // 1. Unwind the "values" array
    {$sort: {'values.key': 1}},  // 2. Sort by "key"
    {$group: {                   // 3. Return only the largest "key"
        _id: '$_id',             //    which is the last item in the group
        val: {$last: '$values'}
    }}
])
{ "_id": ObjectId("5977f8762dbe1d773beb11d0"), "val": { "key": 5, "value": 4 } }

Is this what you have in mind? Note that you can add a $match stage as the first stage to preselect document(s) of interest.

For more information, please see:

Best regards,
Kevin

Asya Kamsky

unread,
Jul 29, 2017, 6:12:20 PM7/29/17
to mongodb-user
While this aggregation will work and return the desired element, I would recommend avoiding $unwind, $sort and $group sequence and instead do the desired array processing in each document in a single $project or $addFields stage.

db.test.aggregate([
  {$addFields:{
    values:{$let:{
       vars: {maxkey:{$max:"$values.key"}},
       in: {$filter:{
            input: "$values”,
            cond:  {$eq:["$$this.key","$$maxkey”]}
       }}
    }}
  }}
])
{ "_id" : ObjectId("5977f8762dbe1d773beb11d0"), "values" : [ { "key" : 5, "value" : 4 } ] }



--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.com/manual/support/
---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/497f7f71-f103-4d79-ab3a-2ccf4c765da0%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--
Asya Kamsky
Lead Product Manager
MongoDB
Download MongoDB - mongodb.org/downloads
We're Hiring! - https://www.mongodb.com/careers

Michael Dolgonos

unread,
Jul 19, 2018, 6:50:15 PM7/19/18
to mongodb-user
Asya,

Using the above example what would be the recommended way of sorting the entire result set by the key filed in the values array?

Thank you
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.

Kevin Adistambha

unread,
Aug 8, 2018, 9:14:51 PM8/8/18
to mongodb-user

Hi Michael,

Using the above example what would be the recommended way of sorting the entire result set by the key filed in the values array?

You can just add a $sort stage into the pipeline, e.g.:

db.test.aggregate([
  {$addFields:{
    values:{$let:{
       vars: {maxkey:{$max:"$values.key"}},
       in: {$filter:{
            input: "$values",
            cond:  {$eq:["$$this.key","$$maxkey"]}
       }}
    }}
  }},
  {$sort:{"values.key":1}}
])

Note that adding a sorting stage would change the memory requirements (and possibly performance) of the query. See $sort Operator and Memory for more details.

If you have more questions, please create a new thread describing your requirements in detail, along with some example documents.

Best regards,
Kevin

Reply all
Reply to author
Forward
0 new messages