Thomas Baird
asked on
Mongoose/MongoDB Aggregation Query Assistance
I'm having difficulty putting a complex(to me) aggregation query together for my data. Basically, here's the run down so everyone understands my conundrum.
I have two collections in this equation. "training_documents" and "users". Each user object has a key identified by name "trainings" which is an array of objects. Each object within this trainings array, contains 4 key/value pairs. An example of each object is below.
The users trainings array will contain many objects following the aforementioned format.
An example user data structure is below for reference.
I need to iterate through each object in the users trainings array during an aggregation. Basically, I need to $lookup the data using the key _id's "document" "trainee" & "trainer" from training_documents, users, & users respectively while keeping them each in their own objects/structure.
I've already tried to put a query together but haven't been able to figure out the iteration piece.
Can someone please guide me on putting this query together? I've been struggling with this for a while and time is running short on delivery.
I have two collections in this equation. "training_documents" and "users". Each user object has a key identified by name "trainings" which is an array of objects. Each object within this trainings array, contains 4 key/value pairs. An example of each object is below.
{
"document": ObjectId('5a0350ad7df0977d94cffab6'),
"trainee": ObjectId('59e51a4b7df0977d94cff95d'),
"trainer": ObjectId('595fcc2e04cf707693257890'),
"completion_time": "2018-04-23T21:28:22.747Z"
}
The users trainings array will contain many objects following the aforementioned format.
An example user data structure is below for reference.
[
{
"_id": "5ad782283c55b056bcc39e3z",
"site": {
"site_id": "site1",
"site_name": "Site One"
},
"user_name": "jsmith",
"first_name": "John",
"middle_name": "A",
"last_name": "Smith",
"full_name": "John Smith",
"title": "Duh Boss...",
"email": "testuser@example.com",
"last_login": "2018-04-27T14:27:27.014Z",
"type": "full-time",
"active": true,
"__v": 0,
"badge_id": "000001780343232123",
"trainings": [
{
"document": ObjectId('5ae33622a766885a121b7362'),
"trainee": ObjectId('5ad782283c55b056bcc39e30'),
"trainer": ObjectId('595fcc2e04cf707693257890'),
"completion_time": "2018-04-23T17:40:41.198Z"
},
{
"document": ObjectId('5a0350ad7df0977d94cffab6'),
"trainee": ObjectId('59e51a4b7df0977d94cff95d'),
"trainer": ObjectId('595fcc2e04cf707693257890'),
"completion_time": "2018-04-23T21:28:22.747Z"
}
],
}
]
I need to iterate through each object in the users trainings array during an aggregation. Basically, I need to $lookup the data using the key _id's "document" "trainee" & "trainer" from training_documents, users, & users respectively while keeping them each in their own objects/structure.
I've already tried to put a query together but haven't been able to figure out the iteration piece.
UserLdap.aggregate([
{ $lookup: {
from: 'training_documents', // Search training documents collection...
localField: 'trainings.document', // Search query is the object id of trainings array.
foreignField: '_id', // key to compare against is _id of trainings[].document.
as: 'trainings.document' // Return results in the trainings array on user.
},
},
]).exec((err, results) => {
if (err) {
console.log(err);
}
else {
res.status(200).json(results);
console.log(results);
}
})
Can someone please guide me on putting this query together? I've been struggling with this for a while and time is running short on delivery.
ASKER
Hi Julian.
Thank you for replying.
I understand I can use the $unwind on my trainings array. While this is likely necessary, I need to try and figure out some sort of $each or $forEach operation on the objects in the trainings array. Unwinding the trainings array will essentially give me an object of objects. Is that a necessary operation.
Thank you for replying.
I understand I can use the $unwind on my trainings array. While this is likely necessary, I need to try and figure out some sort of $each or $forEach operation on the objects in the trainings array. Unwinding the trainings array will essentially give me an object of objects. Is that a necessary operation.
Can you not then do an unwind on the unwind?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Take a look at the unwind statement. You should be able to use this on your array items to aggregate with the documents collection.