Querying an array of arrays in MongoDB

Question

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.

I think your best bet is to change your schema. Documents with arrays of arrays aren't well supported by Mongo. — JohnnyHK
– JohnnyHK, Commented Sep 27, 2012 at 20:57
@JohnnyHK, its quite possible in mongo to query nested arrays. check out my answer — RameshVel
– RameshVel, Commented Sep 28, 2012 at 8:06

RameshVel · Accepted Answer · 2012-09-28 08:05:34Z

163

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" ] ] }

answered Sep 28, 2012 at 8:05

RameshVel

66.1k32 gold badges173 silver badges213 bronze badges

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

6 Comments

Ryan Wheale Over a year ago

Correct me if I'm wrong, but doesn't $elemMatch only return the first result, rendering your "banana" example incorrect?

RameshVel Over a year ago

@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

Ryan Wheale Over a year ago

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...

Curtis Yallop Over a year ago

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"})

Sudan Hari Over a year ago

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 ?

|

KARTHIKEYAN.A · Accepted Answer · 2017-08-18 10:53:50Z

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")})

answered Aug 18, 2017 at 10:53

KARTHIKEYAN.A

20.5k11 gold badges139 silver badges151 bronze badges

1 Comment

TomoMiha Over a year ago

Or if you want to search at 4. array index: { "firstLevel.3": {$elemMatch: {$eq:22}}}

GM Taium Ahmed · Accepted Answer · 2023-04-04 07:59:19Z

-1

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

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

answered Apr 4, 2023 at 7:59

GM Taium Ahmed

1074 bronze badges

1 Comment

Vladimir Kovalchuk Over a year ago

it is not array of arrays

Collectives™ on Stack Overflow

Querying an array of arrays in MongoDB

3 Answers 3

6 Comments

1 Comment

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

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

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

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related