MongoDB query to limit values based on condition

Question

I have a collection like this:

{ "_id" : ObjectId("4d45bd25957a15b80702d4e4"), "codenum" : 10, "payment" : [
 {
  "pcode" : 100,
  "amount" : 10000
 },
 {
  "pcode" : 101,
  "amount" : 6000
 }
], "age" : 70 }

Notice that payment array is embedded in this collection. Now, I would like to retrieve only payment code matching 101 in the find response.

Giving a query like this:

db.test.find({"codenum":10, "payment.pcode": 101}, 
             {"codenum":1, "payment.pcode":1, "payment.amount":1})

will get my entire document including other payment pcodes as well, as in the above display.

However, I require a response something like this:

{ "_id" : ObjectId("4d45bd25957a15b80702d4e4"), "codenum" : 10, "payment" : [
 {
  "pcode" : 101,
  "amount" : 6000
 }
] }

Is this possible in mongodb?

In update(), there is a positional match that can be used like "payment.$.amount" to set the matching field based on the condition.

May be MongoDB can support the query like this:

db.test.find({"codenum":10, "payment.pcode": 101}, 
             {"payment.$.pcode": 1, "payment.$.amount":1})

which would then give only those entries that match the condition given.

Any thoughts?

score 1 · Accepted Answer · answered Jan 30 '11 at 20:20

To my knowledge, it is not possible to select only a single entry from an array in MongoDB. You can return only certain fields from the document (ie, your payment array), but you cannot return only a portion of the array based on the query. (You can use $slice to return a particular index-delimited element, but that doesn't get you what you need here).

You must query either all of the field, or none of it. This JIRA ticket describes the problem, and it is currently "planned but not scheduled".

For the time being, you'll need to just extract the relevant part of the document in your application code.

Thank you. As I browsed, I noticed that http://jira.mongodb.org/browse/SERVER-828 is exactly what I had in mind. Hopefully virtual collection (SERVER-142 the JIRA ticket you mentioned) will be available soon. I will accept your answer. — rsmoorthy, Jan 30 '11 at 20:35

pdxwebdev · Answer 2 · 2013-12-19T06:11:26.533

1

This will get you what you need even though it is still in development. http://www.mongodb.org/display/DOCS/Aggregation+Framework

edited Dec 19 '13 at 06:11

answered Jan 08 '12 at 23:05

pdxwebdev

31
3

I did go through the docs in that URL but I do not find any method to select matching elements alone from the array field. – rsmoorthy Jan 20 '12 at 10:02
What you need to do is $unwind the array field. Then you can just the $match operator to filter the results. Once the results are filtered, you can $project and you should have the record set you're looking for. – pdxwebdev Jan 25 '12 at 20:45

MongoDB query to limit values based on condition

2 Answers2