Query mutiple fields from a nested document of MongoDB

Question

I am using MongoDB 3.0.5. I have a collection like

db.getCollection('mytest').insert([
{_id: 1, "records": [{"Name": "Joe", "Salary": 70000, "Department": "IT"}]},
{_id: 2, "records": [{"Name": "Henry", "Salary": 80000, "Department": "Sales"}, {"Name": "Jake", "Salary": 40000, "Department": "Sales"}]},
{_id: 3, "records": [{"Name": "Sam", "Salary": 90000, "Department": "IT"}, {"Name": "Tom", "Salary": 50000, "Department": "Sales"}]},
{_id: 4, "records":[{"Name":  "Janice", "Salary": 80000, "Department": "Finance"}, {"Name": "Kale", "Salary": 95000, "Department": "IT"}]}
])

Now I could query a single field, say like people in the IT department -

> db.getCollection('mytest').find({"records.Department": {"$in": ["IT"]}}, {"records.$": 1})
{ "_id" : 1, "records" : [ { "Name" : "Joe", "Salary" : 70000, "Department" : "IT" } ] }
{ "_id" : 3, "records" : [ { "Name" : "Sam", "Salary" : 90000, "Department" : "IT" } ] }
{ "_id" : 4, "records" : [ { "Name" : "Kale", "Salary" : 95000, "Department" : "IT" } ] }

I really want to find the salaries in the departments of Finance and IT. But the query with more than one fields returns part of the desired results -

> db.getCollection('mytest').find({"records.Department": {"$in": ["IT", "Finance"]}}, {"records.$": 1})
    { "_id" : 1, "records" : [ { "Name" : "Joe", "Salary" : 70000, "Department" : "IT" } ] }
    { "_id" : 3, "records" : [ { "Name" : "Sam", "Salary" : 90000, "Department" : "IT" } ] }
    { "_id" : 4, "records" : [ { "Name" : "Janice", "Salary" : 80000, "Department" : "Finance" } ] }

I wonder if somebody could help with it. Thanks.

Possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) — styvane, Dec 30 '15 at 18:43
I have seen this post, which is based on one query field. My question is about multiple fields. I am not sure if Mongo 3.2 has solved the similar question. — dapangmao, Dec 30 '15 at 21:01

score 0 · Answer 1 · answered Dec 30 '15 at 23:17

You can do it using mongodb $filter operator. Try the following code:

db.getCollection('mytest').aggregate([
  {  $match: {'records.Department': {$in: ['IT', 'Finance']}}},
  {  $project: { 
      departments: {
        $filter: { 
          input: '$records', 
          as: 'record', 
          cond: {
            $or: [
              {$eq: ['$$record.Department', "IT"]}, 
              {$eq: ['$$record.Department', "Finance"]}
            ]
          }
        } 
      }
    }
  }
])

For your data I got the following result:

{ "_id" : 1, "departments" : [ { "Name" : "Joe", "Salary" : 70000, "Department": "IT" } ] }
{ "_id" : 3, "departments" : [ { "Name" : "Sam", "Salary" : 90000, "Department": "IT" } ] }    
{ "_id" : 4, "departments" : [ { "Name" : "Janice", "Salary" : 80000, "Department" : "Finance" }, { "Name" : "Kale", "Salary" : 95000, "Department" : "IT" } ] }

Query mutiple fields from a nested document of MongoDB

1 Answers1