MongoDb query to get max of field inside array

Question

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.

Ashh · Accepted Answer · 2019-01-29 07:29:06Z

13

You can do using simple $project stage

Something like this

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

edited Jan 29, 2019 at 7:29

answered Sep 24, 2018 at 18:04

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kakashi hatake · Accepted Answer · 2016-04-24 15:15:57Z

5

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/

answered Apr 24, 2016 at 15:15

kakashi hatake

1,1852 gold badges10 silver badges19 bronze badges

Comments

kryshna · Accepted Answer · 2016-04-24 15:12:06Z

0

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]

edited Apr 24, 2016 at 15:12

answered Apr 24, 2016 at 15:04

kryshna

1131 silver badge6 bronze badges

4 Comments

Arvind Singh Over a year ago

using $project throws exception: invalid operator '$max', code 15999

kryshna Over a year ago

what version of mongodb are you using... in that case use unwind as told by @kakashi hatake but use it after your match query to limit the number of unwinded documents

Arvind Singh Over a year ago

MongoDB shell version: 3.0.7. I need max of all unwinded sections but of particular collection._id for which filter is already applied, so can't apply filter on sections.Id.

kryshna Over a year ago

i meant filter with object id first then unwind like this [{"$match": {"_id": ObjectId("571c5c87faf473f40fd0317c")}},{$unwind:"$sections"}], if you unwind first all the documents irrespective of objectId doc you want will be unwinded first

Collectives™ on Stack Overflow

MongoDb query to get max of field inside array

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related