2

What I'm trying to do:

Filter a field of a collection that matches a given condition. Instead of returning every item in the field (which is an array of items), I only want to see matched items.

Similar to

select items from test where items.histPrices=[10,12]

It is also similar to what's found on the mongodb website here: http://www.mongodb.org/display/DOCS/Retrieving+a+Subset+of+Fields

Here's what I have been trying:

db.test.save({"name":"record", "items":[{"histPrices":[10,12],"name":"stuff"}]})
db.test.save({"name":"record", "items":[{"histPrices":[10,12],"name":"stuff"}, 
{"histPrices":[12,13],"name":"stuff"},{"histPrices":[11,14],"name":"stuff"}]})

db.test.find({},{"name":1,"items.histPrices":[10, 12]})

It will return all the objects that have a match for items.histPrices:[10,12], including ALL of the items in items[]. But I don't want the ones that don't match the condition.

From the comments left on Mongodb two years ago, the solution to get only the items with that histPrices[10,12] is to do it with javascript code, namely, loop through the result set and filter out the other items.

I wonder if there's a way to do that with just the query.

Community
  • 1
  • 1
ttback
  • 2,051
  • 5
  • 27
  • 40

3 Answers3

3

Your find query is wrong

   db.test.find({},{"name":1,"items.histPrices":[10, 12]})

Your condition statement should be in the first part of the find statement.In your query {} means fetch all documents similar to this sql

   select items from test (no where clause)

you have to change your mongodb find to 

   db.test.find({"items.histPrices":[10, 12]},{"name":1})

make it work

since your items is an array and if you wanted to return only the matching sub item, you have to use positional operator

  db.test.find({"items.histPrices":[10, 12]},{"name":1,'items.$':1})
RameshVel
  • 64,778
  • 30
  • 169
  • 213
  • This seems simpler and better. – Chien-Wei Huang Nov 03 '12 at 16:55
  • Tried your queries in Try it out on Mongodb.org. First one seems right, but second one doesn't show histPrices of the matched one: [ { "name" : "record", "items" : [ { } ], "_id" : { "$oid" : "50955500cc93742e0d102047" } }, { "name" : "record", "items" : [ { }, { }, { } ], "_id" : { "$oid" : "5095550fcc93742e0d102048" } } ] – ttback Nov 03 '12 at 17:34
  • But this works on my mongodb 2.2. The web page says:"Note: this shell supports only a subset of the real shell's features." Maybe that's the problem. – Chien-Wei Huang Nov 03 '12 at 17:54
  • Yea, i tried again on my local machine. It works. The prod server is using version of 2.0.7 so this didn't work. Will upgrade to 2.2 next week. – ttback Nov 03 '12 at 17:58
  • Postional operator seems to be a v1.4+ feature, let me try again. UPDATE: It doesn't seem to work on 2.0.7 with exactly same query – ttback Nov 03 '12 at 18:00
  • positional operator for update has been around since 1.4 but for find projection it's new in 2.2 – Asya Kamsky Nov 04 '12 at 05:46
1

When working with arrays Embedded to the Document, the best approach is the one suggested by Chien-Wei Huang.

I would just add another aggregation, with the $group (in cases the document is very long, you may not want to retrieve all its content, only the array elements) Operator.

Now the command would look like:

db.test.aggregate({$match:{name:"record"}}, 
{$unwind:"$items"}, 
{$match {"items.histPrices":[10, 12]}}, 
{$group: {_id: "$_id",items: {$push: "$items"}}});)

If you are interested to return only one element from the array in each collection, then you should use projection instead

The same kind of issue solved here: MongoDB Retrieve a subset of an array in a collection by specifying two fields which should match

Community
  • 1
  • 1
Didac Montero
  • 2,046
  • 19
  • 27
0
db.test.aggregate({$unwind:"$items"}, {$match:{"items.histPrices":[10, 12]}})

But I don't know whether the performance would be OK. You have to verify it with your data.

The usage of $unwind

If you want add some filter condition like name="record", just add another $march at first, ex:

db.test.aggregate({$match:{name:"record"}}, {$unwind:"$items"}, {$match:{"items.histPrices":[10, 12]}})
Community
  • 1
  • 1
Chien-Wei Huang
  • 1,773
  • 1
  • 17
  • 27
  • First one failed with JS Error: TypeError: 'undefined' is not a function (evaluating 'db.test.aggregate({$unwind:'$items'},{$match:{'items.histPrices':[10,12]}})') – ttback Nov 03 '12 at 17:37
  • I see..your query is based around mongodb 2.2's aggregation framework? I was on a 2.1.x stack so this apparently didn't work. – ttback Nov 03 '12 at 17:47
  • Yes, my mongodb version is 2.2. – Chien-Wei Huang Nov 03 '12 at 17:50
  • @ttback 2.1.x are unstable development releases; you should upgrade to 2.2 if at all possible. – JohnnyHK Nov 03 '12 at 17:52
  • @JohnnyHK Turns out the prod server has 2.0.7. Upgrade will happen next week, which I'm very happy about. I will try this on my local env and see what happens. – ttback Nov 03 '12 at 17:56
  • Any ideas on how to check the performance of these three solutions? – ttback Nov 03 '12 at 17:59
  • You can insert many records(maybe about 10000) in the collection. Then use each method with a loop running 100000 times and measure the time. I think the performance of RameshVel's solution will be better than using aggregation. – Chien-Wei Huang Nov 03 '12 at 18:14