Hello, @Merce_Bruned_Lacoma! Welcome to the community!
Ok, so we need to have build some kind of relationships between two separate documents. That is achievable only if we combine all the documents into temporary list for calculation purposes.
With this approach, please, note that: depending on the number of documents you take in your aggregation and the size of each document, that will go into that temporary list, you may hit the aggregation pipeline stage memory limitations, that may decrease the aggregation performance.
I will extend your dataset example, so we could run the aggregation against the longer documents chain:
db.test1.insertMany([
{
step: 1,
name: 'house',
score: 18,
},
{
step: 1,
name: 'car',
score: 5,
},
{
step: 2,
name: 'house',
score: 20,
},
{
step: 2,
name: 'boat',
score: 15,
},
{
step: 2,
name: 'yacht',
score: 20,
},
{
step: 3,
name: 'plane',
score: 50,
},
]);
This aggregation should provide you with the desired result:
db.test1.aggregate([
{
$group: {
_id: '$step',
scores: {
$push: {
name: '$name',
score: '$score',
},
},
},
},
{
// we need this, because $group stage
// does not guarantee consistency
// in the order in groups
$sort: {
_id: 1,
},
},
{
$group: {
_id: null,
list: {
// collect all docs into one list
// to be able to compare current and previous doc
$push: '$$CURRENT',
},
},
},
{
$project: {
listWithChainedScores: {
$reduce: {
input: '$list',
initialValue: null,
in: {
$cond: {
if: {
$eq: ['$$value', null],
},
then: {
prev: '$$this',
calculated: ['$$this'],
},
else: {
prev: '$$this',
calculated: {
// concat modified current doc with
// the general list of modified docs
$concatArrays: ['$$value.calculated', [{
// keep the current doc id
_id: '$$this._id',
scores: {
// combine scores of current and previous doc
$setUnion: ['$$this.scores', '$$value.prev.scores'],
},
}]],
},
},
},
},
},
},
},
},
// $unwind + $replaceWith will make a new document
// per each item in the $listWithChainedScores.calculated array
{
$unwind: '$listWithChainedScores.calculated',
},
{
$replaceWith: '$listWithChainedScores.calculated',
},
]).pretty();
Well almost. Here is the output:
[
{
"_id": 1,
"scores": [
{ "name": "house", "score": 18 },
{ "name": "car", "score": 5 }
],
},
{
"_id": 2,
"scores": [
{ "name": "boat", "score": 15 },
{ "name": "car", "score": 5 },
{ "name": "house", "score": 18 }, // duplicate
{ "name": "house", "score": 20 }, // duplicate
{ "name": "yacht", "score": 20 }
],
},
{
"_id": 3,
"scores": [
{ "name": "boat", "score": 15 },
{ "name": "house", "score": 20 },
{ "name": "plane", "score": 50 },
{ "name": "yacht", "score": 20 }
],
},
]
Notice, that there are duplicates in ‘scores’ array.
Resolving those duplicates is tricky here. However, nobody said it is not possible
Add the following stages to the end of the aggregation:
[
{
// $unwind to be able to sort
$unwind: '$scores',
},
{
$sort: {
// order by score from bigger to smaller
// $sort is needed so in the $group stage we picked
// the first score object (that will have the bigger score value)
// change the direction to '1' if you need the opposite
'scores.score': -1,
},
},
{
$group: {
// at this stage we get rid of duplicates
_id: {
docId: '$_id',
scoreName: '$scores.name',
},
scores: {
$first: '$scores',
},
},
},
{
// at this stage we restore the original documents structure
$group: {
_id: '$_id.docId',
scores: {
$push: '$scores',
},
},
},
]
All done.