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?

Chris Heald · Accepted Answer · 2011-01-30 20:20:06Z

1

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.

answered Jan 30, 2011 at 20:20

Chris Heald

62.8k10 gold badges131 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rsmoorthy Over a year ago

Thank you. As I browsed, I noticed that 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.

pdxwebdev · Accepted Answer · 2013-12-19 06:11:26Z

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, 2013 at 6:11

answered Jan 8, 2012 at 23:05

pdxwebdev

313 bronze badges

2 Comments

rsmoorthy Over a year ago

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.

pdxwebdev Over a year ago

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.

Collectives™ on Stack Overflow

MongoDB query to limit values based on condition

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related