Finding highest value from sub-arrays in documents

Question

Let say I have the following collection:

{ _id: 1, Array: [
  { K: "A", V: 8 },
  { K: "B", V: 5 },
  { K: "C", V: 13 } ] }

{ _id: 2, Array: [
  { K: "D", V: 12 },
  { K: "E", V: 14 },
  { K: "F", V: 2 } ] }

I would like to run a query that returns the sub-document with the highest "V", so in that case I would get:

{ _id: 1, Array: [ { K: "E", V: 14 } ] }

or simply:

{ K: "E", V: 14 }

The important part is that I want the memory usage on the Mongo server to be O(1) (no matter how many documents I process, the memory usage is constant), and I only want to retrieve that one subdocument with the value I need (I don't want to download more subdocuments than necessary).

My preferred approach would be to use a simple find query, but I'm not sure if that's possible. I suspect this can be also done with the aggregation framework (or map reduce?), but don't see how. I don't want the result stored in a temporary collection, but directly returned to my client (like a normal query).

You can't do it with a find, but you should be able to with the aggregation framework. — JohnnyHK, Feb 15 '13 at 15:26
I'm stuck on how to collapse all the items from Array into a single subdocument. — Flavien, Feb 15 '13 at 15:30
Take a look at [`$unwind`](http://docs.mongodb.org/manual/reference/aggregation/unwind/#_S_unwind). — JohnnyHK, Feb 15 '13 at 15:33

score 9 · Accepted Answer · edited Feb 18 '13 at 09:00

9

The following aggratation set returns what you need.

db.letters.aggregate([
    {$project:{"Array.K":1, "Array.V":1}},
    {$unwind:"$Array"},
    {$sort:{"Array.V":-1}},
    {$limit:1}
]);

Returns:

{"_id":2, "Array":{"K":"E","V":14}}

Enjoy! :)

edited Feb 18 '13 at 09:00

Sammaye

43,242
7
104
146

answered Feb 15 '13 at 19:53

Victoria Malaya

518
2
8

Sammaye · Answer 2 · 2013-02-18T09:00:37.290

2

As @JohnnyHK said:

db.col.aggregate([
    {$unwind: '$Array'},
    {$group: {_id: '$_id', Array: {K: {$max: '$K'}, V: {$max: '$V'}}}}
])

Something like that.

edited Feb 18 '13 at 09:00

answered Feb 15 '13 at 15:35

Sammaye

43,242
7
104
146

score 0 · Answer 3 · answered Apr 27 '17 at 08:11

IN Simple Words , if you have mongo query Response something like below - and you want only highest value from Array-> "Wish_CreatedDate"

{
  "_id": "57ee5a708e117c754915a2a2",
  "TotalWishs": 3,
  "Events": [
    "57f805c866bf62f12edb8024"
  ],
  "wish": [
    "Cosmic Eldorado  Mountain Bikes, 26-inch (Grey/White)",
    "Asics Men's Gel-Nimbus 18 Black, Snow and Fiery Red Running Shoes - 10 UK/India (45 EU) (11 US)",
    "Suunto Digital Black Dial Unisex Watch - SS018734000"
  ],
  "Wish_CreatedDate": [
    "2017-03-05T00:00:00.000Z",
    "2017-02-13T00:00:00.000Z"
  ],
  "UserDetails": [
    {
      "createdAt": "2016-09-30T12:28:32.773Z",
      "jeenesFriends": [
        "57edf8a96ad8f6ff453a384a",
        "57ee516c8e117c754915a26b",
        "58a1644b6c91d2af783770b0",
        "57ef4631b97d81824cf54795"
      ],
      "userImage": "user_profile/Male.png",
      "email": "roopak@small-screen.com",
      "fullName": "Roopak Kapoor"
    }
  ],

},

***Then you have add

Latest_Wish_CreatedDate: { $max: "$Wish_CreatedDate"},

somthing like below-

{ 
                $project : { _id: 1,
                             TotalWishs : 1 ,
                              wish:1 ,
                               Events:1, 
                               Wish_CreatedDate:1,
                               Latest_Wish_CreatedDate: { $max: "$Wish_CreatedDate"},
                            } 
            }

And Final Query Response will be below

{
  "_id": "57ee5a708e117c754915a2a2",
  "TotalWishs": 3,
  "Events": [
    "57f805c866bf62f12edb8024"
  ],
  "wish": [
    "Cosmic Eldorado  Mountain Bikes, 26-inch (Grey/White)",
    "Asics Men's Gel-Nimbus 18 Black, Snow and Fiery Red Running Shoes - 10 UK/India (45 EU) (11 US)",
    "Suunto Digital Black Dial Unisex Watch - SS018734000"
  ],
  "Wish_CreatedDate": [
    "2017-03-05T00:00:00.000Z",
    "2017-02-13T00:00:00.000Z"
  ],
  "UserDetails": [
    {
      "createdAt": "2016-09-30T12:28:32.773Z",
      "jeenesFriends": [
        "57edf8a96ad8f6ff453a384a",
        "57ee516c8e117c754915a26b",
        "58a1644b6c91d2af783770b0",
        "57ef4631b97d81824cf54795"
      ],
      "userImage": "user_profile/Male.png",
      "email": "roopak@small-screen.com",
      "fullName": "Roopak Kapoor"
    }
  ],
  "Latest_Wish_CreatedDate": "2017-03-05T00:00:00.000Z"
},

Finding highest value from sub-arrays in documents

3 Answers3

Linked

Related