How to write Mongo query to find sub document with condition

Question

I have a document in a collection like this, I need to find the record with form_Id:1 and Function_Id:2, how to write the mongo query.

"Form_Id" : 1,
"Function" : [{
  "Function_Id" : 1,
  "Role" : [{
      "Role_Id" : 1,
      "UserId" : ["Admin", "001"]
    }]
}, {
  "Function_Id" : 2,
  "Role" : [{
      "Role_Id" : 2,
      "UserId" : ["Admin", "005"]
    }]
}]

Is the any possibility get the record like this `{ "Function_Id" : 2, "Role" : [{ "Role_Id" : 2, "UserId" : ["Admin", "005"] }` as my result. — siva, Feb 26 '13 at 05:35

score 24 · Accepted Answer · answered Feb 26 '13 at 15:18

24

You can use dot notation and the $ positional projection operator to do this:

db.test.find({Form_Id: 1, 'Function.Function_Id': 2}, {_id: 0, 'Function.$': 1})

returns:

{"Function": [{"Function_Id": 2, "Role": [{"Role_Id": 2, "UserId": ["Admin", "005"]}]}]}

answered Feb 26 '13 at 15:18

JohnnyHK

305,182
66
621
471

@JhonnyHK I tried your answer, it results like this `{ "Function" : [ { }, { } ] }` ,May I know what am doing wrong in this. – siva Feb 27 '13 at 05:21
@JhonnyHK I don't know where I did mistake, your query gave the exact result, I tried the same with c# application as `var Logs = db.GetCollection("Access_Rights").FindAs(query).SetFields(Fields.Exclude("_id",Function.Role")).SetFields(Fields.Include("Function.$"));` was am doing correct way. please guide me. – siva Mar 02 '13 at 07:28

ann · Answer 2 · 2013-02-26T14:44:34.683

6

Since your function key is an array, in order to use the $match operator, first you have to use the $unwind operator. http://docs.mongodb.org/manual/reference/aggregation/unwind/ And then you use $match operator to find the documents that you want http://docs.mongodb.org/manual/reference/aggregation/match/

So your query should look like this

    db.collection.aggregate([{$unwind:"$Function"},{$match:{"Form_id":1,"Function.Function_id":2}}])

By default mongo will display the _id of the document. So if you do not want to display the _id, after matching the relevant ones, you could use the $project operator http://docs.mongodb.org/manual/reference/aggregation/project/

     db.collection.aggregate([{$unwind:"$Function"},{$match:{"Form_id":1,"Function.Function_id":2}},{$project:{"_id":0,"Form_id":1,"Function":1}}])

If you don't want the form_id to be displayed, simply don't specify the form_id in the project part of the query. By default mongo will only display the keys whose value is 1. If the key is not mentioned it will not display it.

    db.collection.aggregate([{$unwind:"$Function"},{$match:{"Form_id":1,"Function.Function_id":2}},{$project:{"_id":0,"Function":1}}])

edited Feb 26 '13 at 14:44

answered Feb 26 '13 at 05:54

ann

602
7
13

I tried your answer in my db, getting type error aggregate is not a function shell. – siva Feb 26 '13 at 13:24
Which version of mongo do you have, because I have tried out all the queries before posting the answer and it works. Aggregation is supported from mongo 2.1 onwards [link](http://docs.mongodb.org/manual/applications/aggregation/#overview) – ann Feb 26 '13 at 14:49
I 'am using mongodb version win32-i386-2.0.7, So the functionality what am trying can't achieve in this version, Is there any way to do this in this version. – siva Feb 27 '13 at 05:11
I tried your code in latest version MongoDB 2.2.3, it results like `{ "result" : [ ], "ok" : 1 }`. Did I have to make any changes in my document. Pls guide me. – siva Feb 27 '13 at 08:47
In my reply, that I have posted `Form_id` instead of `Form_Id` . I think that is the issue. And are you giving the correct collection name? If it still doesn't work, you could just post your query. – ann Feb 27 '13 at 17:59
sorry for late reply @ann `db.Access.aggregate([{$unwind:"$Function"},{$match:{"Form_Id":1,"Function.Function_Id":2}},{$project:{"_id":0,"Function":1}}])` this is the query i tried in my db. – siva Mar 02 '13 at 06:56
@siva I have tried your query and this time with Access as my db. This is the result I get `"result":[{"Function":{"Function_Id":2,"Role":[{"Role_Id":2,"UserId":["Admin",5]}]}}],"ok":1` – ann Mar 02 '13 at 15:20
This time I got the result as you shown while executing the same query. Is it possible to get only the function_Id. Result like `"Function" : [{ "Function_Id" : 1}]` . – siva Mar 05 '13 at 15:05
I think as of now the top level id field can only be excluded – ann Mar 06 '13 at 10:56

How to write Mongo query to find sub document with condition

2 Answers2

Linked