Link to home
Start Free TrialLog in
Avatar of Thomas Baird
Thomas BairdFlag for United States of America

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.

    {
    	"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.
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

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.
Avatar of Thomas Baird

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.
Can you not then do an unwind on the unwind?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members 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.