2

maybe somebody knows.. Each document in my mongoDB collection looks like:

[
  _id: 1,
  login: user1,
  strength: {
    a: 5,
    b: 2
  },
  group: 2
]

How to find first 10 documents in each group with the greatest sum of strength.a and strength.b?

Example: I have documents like this:

[ login: login1, group: 2, strength: { a: 2, b: 5 } ]

[ login: login2, group: 1, strength: { a: 1, b: 2 } ]

[ login: login3, group: 3, strength: { a: 1, b: 4 } ]

[ login: login5, group: 1, strength: { a: 1, b: 1 } ]

[ login: login6, group: 1, strength: { a: 10, b: 4 } ]

[ login: login7, group: 3, strength: { a: 0, b: 2 } ]

[ login: login8, group: 3, strength: { a: 11, b: 4 } ]

I want to get result:

[ group: 1, users: [ { login: login6, sumOfStrength: 14 }, { login: login2, sumOfStrength: 3 } ] ]

[ group: 2, users: [ { login: login1, sumOfStrength: 7 } ] ]

[ group: 3, users: [ { login: login8, sumOfStrength: 15 }, { login: login3, sumOfStrength: 5 } ] ]

In conclusion, In this example I want to get results grouped by the "group" field, and select only the first two documents with the greatest total of "a" and "b" fields from each group. Is this feasible?

Until now I did it:

db.collection('myCollection').aggregate( 
[ 
  { $group: { _id: '$group', users: { $push: { login: '$login', sumOfStrength: { $sum: [ '$strength.a', '$strength.b'] } } } } } 
] )

But how sort and limit number of elements in the array "users" for each group?

nelkor
  • 89
  • 4

2 Answers2

0

Here some directions :

  1. sum aand bfields first(Grouping with _idfield) into a new field for each documents (you can do this in aggregation or you can add a new field in your document structure. I would add a new field so you can create an index with this field)
  2. sortdocuments with groupand new created a+b fields
  3. make your groupoperation and push(push will be already sorted)
  4. Use slice to get only first 2 elements of users array
barbakini
  • 3,024
  • 2
  • 19
  • 25
0

It's almost done, I have:

db.collection('xxx').aggregate( [ 

    { $project: { _id: 1, group: 1, sumOfStrength: { $sum: [ '$strength.a', '$strength.b'] } } },   
    { $sort: { group: 1, sumOfStrength: -1 } },
    { $group: { _id: '$group', users: { $push: { login: '$login', sumOfStrength: '$sumOfStrength' } } } }
]);

Where I have to put $slice? Somewhere in $group operation or in another $project? I have no idea..

nelkor
  • 89
  • 4