Mongoose/MongoDB Aggregation Query Assistance

Thomas Baird
Thomas Baird used Ask the Experts™
on
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.

    {
    	"document": ObjectId('5a0350ad7df0977d94cffab6'),
    	"trainee": ObjectId('59e51a4b7df0977d94cff95d'),
    	"trainer": ObjectId('595fcc2e04cf707693257890'),
    	"completion_time": "2018-04-23T21:28:22.747Z"
    }

Open in new window


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"
                        }
    		],
    	}
    ]

Open in new window


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

Open in new window


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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I can't give you a full solution to the problem but can try to talk you through it.

Take a look at the unwind statement. You should be able to use this on your array items to aggregate with the documents collection.
Thomas BairdIT Manger

Author

Commented:
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.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you not then do an unwind on the unwind?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial