CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Arvind Singh
Arvind Singh

Reputation: 792

MongoDb query to get max of field inside array

How to get the maximum of sections.Id in below document where collection._id = some parameter

{
    "_id" : ObjectId("571c5c87faf473f40fd0317c"),
    "name" : "test 1",
    "sections" : [ 
        {
             "Id" : 1,
             "name" : "first section"
        }, 
        {
            "Id" : 2,
            "name" : "section 2"
        }, 
        {
            "Id" : 3,
            "name" : "section 3"
        }
}

I have tried below 

db.collection.aggregate(
[
    {
        "$match": {
            "_id": ObjectId("571c5c87faf473f40fd0317c")
        }
    },
    {
        "$group" : {
            "_id" : "$_id",
            "maxSectionId" : {"$max" : "$sections.Id"}
        }
    }
]);

But instead of returning max int single value it is returning an array of all Ids in sections array.

Further same query when executed in node.js it returns an empty array.

Upvotes: 4

Views: 8769

Answers (3)

Ashh
Ashh

Reputation: 46441

You can do using simple $project stage

Something like this 

db.collection.aggregate([
  { "$project": {
    "maxSectionId": {
      "$arrayElemAt": [
        "$sections",
        {
          "$indexOfArray": [
            "$sections.Id",
            { "$max": "$sections.Id" }
          ]
        }
      ]
    }
  }}
])

Upvotes: 13

kakashi hatake
kakashi hatake

Reputation: 1185

your aggregation query need $unwind for opennig to "sections" array

add your aggregation query this

{$unwind : "$sections"}

and your refactoring aggregation query like this 

db.collection.aggregate(
[  
    {$unwind : "$sections"},
    {
        "$match": {
            "_id": ObjectId("571c5c87faf473f40fd0317c")
        }
    },
    {
        "$group" : {
            "_id" : "$_id",
            "maxSectionId" : {"$max" : "$sections.Id"}
        }
    }
]);

and more knowledge for $unwind : https://docs.mongodb.org/manual/reference/operator/aggregation/unwind/

Upvotes: 5

kryshna
kryshna

Reputation: 113

Replace $group with $project

In the $group stage, if the expression resolves to an array, $max does not traverse the array and compares the array as a whole.

With a single expression as its operand, if the expression resolves to an array, $max traverses into the array to operate on the numerical elements of the array to return a single value [sic]

Upvotes: 0

Related Questions