Getting document with max size of inner array in MongoDB

Question

Sample Mongo Document:

{
    "_id": "128374",
    "x": [
        {
            "i": "83847575",
            "y": [
                {
                    "i": "9389489283",
                    "t": "2014-04-11T20:46:57+0000"
                },
                {
                    "i": "9389489284",
                    "t": "2014-04-11T20:47:57+0000"
                }
            ]
        },
        {
            "i": "83847576",
            "y": [
                {
                    "i": "2382349385",
                    "t": "2014-01-15T23:43:29+0000"
                },
                {
                    "i": "9389489286",
                    "t": "2014-04-11T20:47:57+0000"
                },
                {
                    "i": "9389489286",
                    "t": "2014-04-11T20:49:57+0000"
                }
            ]
        }
    ]
}

How do you get max count of inner array 'y' per document? The problem I'm trying to solve is to get the record which has max number of 'y's. Thanks!

The following gives me the total count of 'y'.

db.coll.aggregate( 
{ "$unwind" : "$x" } , 
{ "$project" : { "x" : "$x" } } , 
{ "$unwind" : "$x.y" }, 
{ "$group" : { _id : null, number : { $sum : 1 } } } )

Answer 1

If you want to find the document in the collection that has the most "y" elements in it the way to do that in aggregation framework would be like this:

db.coll.aggregate( {$unwind:"$x"},
                   {$project:{ysize:{$size:"$x.y"}}},
                   {$group:{_id:"$_id",numYs:{$sum:"$ysize"}}},
                   {$sort: {numYs:-1} },
                   {$limit: 1 }
)

This uses the $size operator (new in 2.6) to project size of the inner y arrays in each document, and the group adds them back up to calculate how many "y" elements each document has.

Adding sort and limit allows you to keep the top one, or top N.

This gives you the order by total number of y elements across the entire document. If you wanted to just know which document has the longest array y in it, it's even simpler, just take out the group and sort on ysize:

db.coll.aggregate( {$unwind:"$x"},
                   {$project:{ysize:{$size:"$x.y"}}},
                   {$sort: {ysize:-1} },
                   {$limit: 1 }
)