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

Answer 2

All queries, when you need to group by sum values called aggregate. You can read about it in the mongo docs and same methods have model in Mongoose. To produce your query, you can use code like this:

Employee
  .aggregate()
  .group({ _id: '$locName', empList: { $push: "$$ROOT" }})
  .exec(function (err, res) {

});

If you need not to query all table, there is also have a match method.