MongoDB Select MIN/MAX from values inside Array

Question

So I'm having issues performing a Query to return just two values, MIN and MAX for a field inside an Array field...

client.files.uploads is the field I want to get the MIN/MAX.

The result will be just "0,10" for example, I don't want to get more fields, just look into every client.files.uploads field and get the MIN/MAX uploads whole clientcollection has, but return only the lowest and highest value not a row for each client.

This is my query, but is not getting me what I want...

db.client.aggregate([
    {$unwind: "$files"},
    { 
        $group: { 
            _id : "$_id",
            data_min: {$min: "$files.uploads"},
            data_max: {$max: "$files.uploads"}
        }
    },
    {
        $project:{
            data_min: "$min",
            data_max: "$max",
        }
    }
],{allowDiskUse:true})

UPDATE:

I've managed to do what I wanted, but the question still open because I don't thing my way is the best way to achieve it...

db.clients.aggregate([
  {$unwind: "$files"},
  {
    $group: {
      _id: "$_id",
      data_min: {$min: "$files.uploads"},
      data_max: {$max: "$files.uploads"}
    }
  },
  {

    $group: {
      _id: "1",
      min: {$min: "$data_min"},
      max: {$max: "$data_max"}
    }
  },
  {
    $project: {
      _id: 0,
      min: 1,
      max: 1
    }
  }
],
{
  allowDiskUse: true
})

This query is returning me one row only, with the lowest and highest value for the whole collection, which is what I wanted.

This is an example data of my documents

[
  {
    "name": "John",
    "files": [
      {
        "uploads": 9685,
        "downloads": 83,

      },
      {
        "uploads": 1,
        "downloads": 833
      },
      {
        "uploads": 676,
        "downloads": 823
      }
    ]
  },
  {
    "name": "Peter",
    "files": [
      {
        "uploads": 32,
        "downloads": 99
      },
      {
        "uploads": 34,
        "downloads": 4
      }
    ]
  },
  {
    "name": "Mery",
    "files": [
      {
        "uploads": 3,
        "downloads": 244
      },
      {
        "uploads": 15,
        "downloads": 543
      },
      {
        "uploads": 1345,
        "downloads": 22
      },
      {
        "uploads": 6743,
        "downloads": 87543
      }
    ]
  }
]

Sede · Accepted Answer · 2015-04-13 10:45:07Z

4

Your projection is wrong. You are using accumulator operator as values. Change your projection to this:

{
    $project:{
        _id: 0,
        data_min: 1,
        data_max: 1
    }
}

answered Apr 13, 2015 at 10:45

Sede

61.5k20 gold badges159 silver badges162 bronze badges

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

4 Comments

Robert W. Hunter Over a year ago

Your answer is correct but I've changed my question as it seemed to be wrong, i not only want to get thoose two fields only (which is your answer and is 100% correct) i meant I wanted to return only ONE row, with the lowest and highest values for the whole collection, something that I've accomplished with two groups, but Maybe there are better options (see my update)

Sede Over a year ago

your aggregation is good but in your second $group use _id: null instead of _id: "1"

Robert W. Hunter Over a year ago

Okay, thank you, I've changed it. I thought that maybe my query was not good (even if it works) because of the two groups, and maybe there were another way to achieve it.. Thank you

Robert W. Hunter Over a year ago

Yeah sorry I was away and totally forgot about stackoverflow!

Neo-coder · Accepted Answer · 2015-04-13 11:37:24Z

0

Check this aggregation query

db.clients.aggregate({"$unwind": "$files"},
                     {"$project":{"name":"$name","files":"$files"}},
                     {"$group":{"_id":"$_id","max":{"$max":"$files"},
                                             "min":{"$min":"$files"}}
                     }).pretty()

Or use $project as below

db.clients.aggregate({"$unwind": "$files"},
                     {"$project":{"name":"$name","files":"$files"}},
                     {"$group":{"_id":"$_id","max":{"$max":"$files"},
                                              "min":{"$min":"$files"}}},
                     {"$project":{"max_upload":"$max.uploads",
                     "max_download":"$max.downloads",
                     "min_upload":"$min.uploads",
                     "min_downloads":"$min.downloads","_id":0
                                  }
                     })

edited Apr 13, 2015 at 11:37

answered Apr 13, 2015 at 11:31

Neo-coder

7,8565 gold badges36 silver badges54 bronze badges

1 Comment

Sede Over a year ago

It will not work. Just tested. Also what is wrong with OP query?

Collectives™ on Stack Overflow

MongoDB Select MIN/MAX from values inside Array

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related