60

Say, I have a document like this..

"ID" : "fruit1",
"Keys" : [["apple", "carrot", "banana"]]

How do I query for Keys = "carrot". None of the following syntax works.

db.myColl.results.find({ "Keys" : "carrot" });
db.myColl.results.find({ "Keys" : [["carrot"]] });

Following works though, but not helpful.

db.myColl.results.find({ "Keys" : [["apple", "carrot", "banana]]});

Any pointer to this query will be helpful. Thanks.

Brad C
  • 2,868
  • 22
  • 33
rajibdotnet
  • 1,498
  • 2
  • 17
  • 29

3 Answers3

159

Interesting question, This will do the trick

 db.multiArr.find({'Keys':{$elemMatch:{$elemMatch:{$in:['carrot']}}}})

$elemMatch used to check if an element in an array matches the specified match expression. so nested $elemMatch will go deeper into nested arrays

Test data     

db.multiArr.insert({"ID" : "fruit1","Keys" : [["apple", "carrot", "banana"]]})
db.multiArr.insert({"ID" : "fruit2","Keys" : [["apple", "orange", "banana"]]})


db.multiArr.find({'Keys':{$elemMatch:{$elemMatch:{$in:['carrot']}}}})
{ "_id" : ObjectId("506555212aeb79b5f7374cbf"), "ID" : "fruit1", "Keys" : [ [ "apple", "carrot", "banana" ] ] }

db.multiArr.find({'Keys':{$elemMatch:{$elemMatch:{$in:['banana']}}}})

{ "_id" : ObjectId("506555212aeb79b5f7374cbf"), "ID" : "fruit1", "Keys" : [ [ "apple", "carrot", "banana" ] ] }
{ "_id" : ObjectId("5065587e2aeb79b5f7374cc0"), "ID" : "fruit2", "Keys" : [ [ "apple", "orange", "banana" ] ] }
RameshVel
  • 64,778
  • 30
  • 169
  • 213
  • Correct me if I'm wrong, but doesn't $elemMatch only return the first result, rendering your "banana" example incorrect? – Ryan Wheale Jun 15 '14 at 21:01
  • @RyanWheale, i don't get it. $elemMatch returns all records matching the given condition, not just the first one. In the banana example, it does return 2 records. you can check the ids – RameshVel Jun 16 '14 at 07:37
  • 1
    My bad. Using `$elemMatch` for **projection** only returns one result. Using it in a query will return multiple results as you would expect. Lack of sleep is getting to me... – Ryan Wheale Jun 16 '14 at 09:39
  • 2
    Alternatively consider using an array of subdocument of array. db.multiArrTest.insert({"ID" : "fruit1","Keys" : [{"array" : ["apple", "carrot", "banana"]}, {"array": ["a", "b", "c"]}]}) db.multiArrTest.find({'Keys.array': "carrot"}) – Curtis Yallop Jul 02 '14 at 17:08
  • I have an scenario, where I need to find sub documents. could I able to find it using the $elemMatch ? My data schema some thing like this { "id" : 1, "data: { "user": [ { 'id' : 1, 'name' : ABC, }, { 'id' : 2, 'name' : ABC, }, { 'id' : 3, 'name' : ABC, }, ] } } Need to find get the object where the user's id =3; Can someone help me to sort this ? – Sudan Hari May 06 '15 at 13:47
  • @RameshVel Do you know how to update `carrot` when we don't know indexOf `carrot` ? – Emad Emami Jan 06 '17 at 10:06
3

if you want to find array of first position data, use 0 as reference index to get data in nested array.

db.getCollection('routes').find({"routeId" : 12,'routes._id':ObjectId("598da27a713f3e6acd72287f"),'routes.0.stops.0.orders.0._id':ObjectId("598da27a713f3e6acd722887")})
KARTHIKEYAN.A
  • 18,210
  • 6
  • 124
  • 133
-1

"merchant_id": [ { "id": "63fa3cd15595d06245a950ed" }, { "id": "63fa3cd15595d06245a950ed" } ],

{ merchant_id: { $elemMatch: { id: merchant } } }

GM Taium Ahmed
  • 77
  • 4