MongoDB query multiple subdocuments

Question

My MongoDB data structure looks like this:

{
  users: [{
    userref: "User1",
    results: [{
      testref: "TEST1",
      history: [{
        score: 10
      }, {
        score: 15
      }]
    }, {
      testref: "TEST2",
      history: [{
        score: 2
      }, {
        score: 1
      }]
    }]
  }]
}

I have a collection of users, each with an array of results (one result object for each test), and within each result, an array of the history of the historical scores.

What I need to do is "return the userref for all users whose most recent score for TEST1 is X and whose most recent score for TEST2 is Y". Users who have not completed one or other of these tests should not be returned. By "most recent", I mean the last in the array.

I'm assuming I need to use aggregation, but I'm totally new to this, so could do with some help.

This is what I have tried so far, and it works, but it only matches the condition where TEST1=15, and I would also like it to match the condition TEST2=1.

[{$unwind: {
  path: '$results’,
  preserveNullAndEmptyArrays: false
}}, {$match: {
  'results.testref': 'TEST1'
}}, {$project: {
  results: 1,
  userref: 1,
}}, {$replaceRoot: {
  newRoot: {
    $mergeObjects: [
      {
        userref: '$userref'
      },
      '$results'
    ]
  }
}}, {$project: {
  userref: 1,
  history: {
    $slice: [
      '$history',
      -1
    ]
  }
}}, {$match: {
  'history.score': 15
}}, {$project: {
  userref: 1,
}}]

Thanks

Have you tried something? What do you mean by recent based on your data? — Gibbs
– Gibbs, Commented Jul 9, 2020 at 14:33
Thanks, @Gibbs, I have added more detail and the aggregation I have started on. It seems very clunky to me, so I am sure there is a better way of doing it. By "most recent", I mean the last entry in the history array. — EricP
– EricP, Commented Jul 9, 2020 at 15:31

Gibbs · Accepted Answer · 2020-07-09 19:31:23Z

1

To match both the conditions,

db.getCollection('test2').aggregate([{"$unwind":"$users"}, {$unwind:"$users.results"}, 
{$project:
     {"lastScore": 
        {$slice:
            ["$users.results.history", -1]
        },
     "users.userref":1,
     "users.results.testref":1
    }
},
{
    $match:{"$or":[{
      $and:[
        {"users.results.testref":"TEST1"},
        {"lastScore.0.score":15}
      ]},
      {
      $and:[
        {"users.results.testref":"TEST2"},
        {"lastScore.0.score":1}
      ]}
      ]
    }
},
{
    $group:{
        "_id":{"_id":"$_id","userref":"$users.userref"},
        "data":{$push:{"tests":"$users.results.testref", "userref":"$users.userref"}}
    }
},
{
    $project:{
        "_id":1,
        "validUser":{
        "$filter":{
            "input":"$data",
            "as":"data",
            "cond":{$or:[{$eq:["$$data.tests","TEST1"]}, {$eq:["$$data.tests","TEST2"]}]}
        }}
    }
},
{
    $project:{
        "_id.userref":1
    }
}
])

What does the query do:

Unwind users
Unwind results
Get last score part along with user ref, test ref
Check for the necessary conditions
Group back on user ref along with doc id
Check whether two conditions met using $filte
Project userref

Output:

/* 1 */
{
    "_id" : {
        "userref" : "User1"
    }
}

answered Jul 9, 2020 at 19:31

Gibbs

23.1k14 gold badges84 silver badges147 bronze badges

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

2 Comments

EricP Over a year ago

This was a huge help, thanks so much for your time. I made a couple of small tweaks: as it is, the filter returns any users who have completed either one of the tests, and I needed it to only return those users who had completed both tests, so I changed it to a $match with : {$and: [ {"data": {$elemMatch: {tests: "CONF"}}}, {"data": {$elemMatch: {tests: "PB"}}} ]}. I also did a $replaceRoot { newRoot: { userref: '$_id.userref' } } at the end to get rid of the _id container of the userref. Would have struggled a long time without your eyes, thanks @Gibbs.

Gibbs Over a year ago

Glad, it helped. Happy coding @EricP

Collectives™ on Stack Overflow

MongoDB query multiple subdocuments

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related