Mongoose find with multiple matches

Question

I'm new to this technology and working with Node and Express server that uses Mongoose. I have following schema for a document collection.

var empSchema = new mongoose.Schema({
	_id: String,
	orgName: {type: String, required: true},
	locName: {type: String, required: true},
	empName: {type: String, required: true}
});

Here I get a list of location names like "NewYork", "London", "Paris" etc... in a request and needs to return the documents in the response as following....

{
result:[{locName:"NewYork",
	empList:[
		{orgName:"abc", empName:"emp1"},
		{orgName:"xyz", empName:"emp2"}]
	},
	{locName:"London",
	empList:[
		{orgName:"pkq", empName:"emp13"},
		{orgName:"mns", empName:"emp23"}]
	}]
}

What would be the best way to use mongoose from Node. I think making multiple queries (each one with a location) to mongodb is a bad idea.

Is there a way to get the expected json response with single call to mongoose? Thanks.

Answer 1

Yes, use the aggregation framework to get the desired output. The aggregation pipeline will consist of a $group operator pipeline stage which groups the documents by the locName field and the $addToSet accumulator operator to add the orgName and empName fields to an array empList. The last pipeline stage $project operator then replaces the _id field from the previous aggregation stream with a new field locName.

To demonstrate this concept, suppose you have a sample collection which you insert with mongo shell:

db.employees.insert([
    {
        _id: "1",
        orgName: "abc",
        locName: "New York",
        empName: "emp1"
    },
    {
        _id: "2",
        orgName: "xyz",
        locName: "New York",
        empName: "emp2"
    },
    {
        _id: "3",
        orgName: "pkq",
        locName: "London",
        empName: "emp13"
    },
    {
        _id: "4",
        orgName: "mns",
        locName: "London",
        empName: "emp23"
    }
])

The following aggregation produces the desired result:

db.employees.aggregate([
    {
        "$group": {
            "_id": "$locName",
            "empList": {
                "$addToSet": {
                    "orgName": "$orgName",
                    "empName": "$empName"
                }
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "locName": "$_id",
            "empList": 1
        }
    }
])

Output:

/* 0 */
{
    "result" : [ 
        {
            "empList" : [ 
                {
                    "orgName" : "mns",
                    "empName" : "emp23"
                }, 
                {
                    "orgName" : "pkq",
                    "empName" : "emp13"
                }
            ],
            "locName" : "London"
        }, 
        {
            "empList" : [ 
                {
                    "orgName" : "xyz",
                    "empName" : "emp2"
                }, 
                {
                    "orgName" : "abc",
                    "empName" : "emp1"
                }
            ],
            "locName" : "New York"
        }
    ],
    "ok" : 1
}

In Mongoose, you can use the aggregation pipeline builder like this:

Employee.aggregate()
        .group({
            "_id": "$locName",
            "empList": {
                "$addToSet": {
                    "orgName": "$orgName",
                    "empName": "$empName"
                }
            }
        })
        .project({
            "_id": 0,
            "locName": "$_id",
            "empList": 1
        })
        .exec(function (err, res) {
            if (err) return handleError(err);
            console.log(res); 
    });

// Or the simple aggregate method
var pipeline = [
    {
        "$group": {
            "_id": "$locName",
            "empList": {
                "$addToSet": {
                    "orgName": "$orgName",
                    "empName": "$empName"
                }
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "locName": "$_id",
            "empList": 1
        }
    }
]

Employee.aggregate(pipeline, function (err, res) {
    if (err) return handleError(err);
    console.log(res); 
});