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 } } } )

Why are you grouping on null?

WiredPrairie
– WiredPrairie

2014-04-25 23:11:05 +00:00
Commented Apr 25, 2014 at 23:11 — WiredPrairie
– WiredPrairie, Commented Apr 25, 2014 at 23:11
how do you find size of x on this, as x is an array itself

kavinder
– kavinder

2015-09-15 06:25:05 +00:00
Commented Sep 15, 2015 at 6:25 — kavinder
– kavinder, Commented Sep 15, 2015 at 6:25

Asya Kamsky · Accepted Answer · 2014-04-26 07:11:27Z

4

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 }
)

answered Apr 26, 2014 at 7:11

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

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

1 Comment

Asya Kamsky Over a year ago

when you upgrade to 2.6 the $size operator will eliminate the need to unwind the y array to then group it back just to count its size.

turivishal · Accepted Answer · 2021-05-14 14:17:46Z

1

In order to count the inner elements you would need something to anchor on for a grouping. In this case you can use the i element that is at the same level as y:

db.collection.aggregate([
    { "$unwind" : "$x" }, 
    { "$unwind" : "$x.y" }, 
    { "$group" : { 
        "_id" : {
          "_id": "$_id",  
          "xi":"$x.i"
        },
        "number" : { "$sum" : 1 } 
    }},
    { "$sort": { "number": -1 }},
    { "$limit": 1 } 
])

So by compounding the grouping key with the document _id you can then match the document that has the largest inner value of y as a count by sorting that to the top.

edited May 14, 2021 at 14:17

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

answered Apr 25, 2014 at 23:15

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Comments

Derek Soike · Accepted Answer · 2016-08-23 16:38:14Z

0

Try this aggregation query.

db.collection.aggregate([
    {$unwind: '$x'},
    {$group: {_id: null, maxLengthOfYArray: {$max: {$size: '$x.y'}}}}
])

answered Aug 23, 2016 at 16:38

Derek Soike

11.7k3 gold badges83 silver badges77 bronze badges

Collectives™ on Stack Overflow

Getting document with max size of inner array in MongoDB

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related