How To Find Document With Child Array Where Child Has An Id

Question

I hope someone can help.

I have inherited a project using an atlas mongo db. I has been asked to get data from a collection. Assume documents are structured in the collection like this:

{ _id: 'Parent1', kids: [ { _id: 'Test1', grandkids: [ { name: 'Blah' } ] } ] },
{ _id: 'Parent2', kids: [ { _id: 'Test1', grandkids: [ { name: 'Bloh' } ] } ] },
{ _id: 'Parent3', kids: [ { _id: 'Test2', grandkids: [ { name: 'Bloh' } ] } ] }

I need to get the grandkid with the name 'Bloh' when the kid's _id is 'Test 1', which should return data from the 2nd document.

Using the Cloud MongoDb web ui, I tried querying like this, jsut to see if I could get the 2nd document back:

{
 '$and': [
 { 'kids._id': ObjectId('Test 1') },
 { 'kids.grandkids.name': 'Bloh' }
 ]
}

But I get 0 results. I suspect it might be because the _id in the kids is not unique.

Assuming I can get the 2nd doucment back, how do I just get the matching grandkid back?

Thanks in advance!

Edit:

Using the MongoDb playground, this might be problem:

Invalid query:

Line 3: Invalid ObjectId: hash has to be 24 char long

Unfortunately, kid._id is not only duplicated, it's also not always 24 characters long.

Answer 1

Wrt

Using the MongoDb playground, this might be problem:
Invalid query:
Line 3: Invalid ObjectId: hash has to be 24 char long

Well, in your example, ObjectId('Test 1') is invalid but in your document, it's just _id: 'Test1' which is a string and that's okay. If you are using ObjectIds in your actual data, then replace both of those cases with ObejctId in the query below.

Use $elemMatch to make sure both the conditions kids._id and kids.grandkids.name belong to the same kid in the kids array:

db.collection.find({
 kids: {
 $elemMatch: {
 _id: "Test1",
 "grandkids.name": "Bloh"
 }
 }
})

Mongo Playground

---

In your query, if it was like this (not ObjectId), then kids._id and kids.grandkids.name don't have to belong to the same kid object in the kids array. Also, you put 'Test 1' with an extra space instead of 'Test1' :

{
 '$and': [
 { 'kids._id': 'Test1' },
 { 'kids.grandkids.name': 'Bloh' }
 ]
}

Which works but is actually incorrect. See the extra match which occurs (I added some data) for 'Parent 4' and that extra match is wrong. It's matching the _id from the first grandkid and the name on the second grandkind. Even shortening it to the equivalent term without $and yields the same incorrect results:

db.collection.find({
 "kids._id": "Test1",
 "kids.grandkids.name": "Bloh"
})

• Orion: is there a way to project the grandkid(s) that get returned from the query?
• Orion: Your query worked in MongoDb playground - it returned documents. I tried the query in the Atlas hosted db, & no documents were returned (the names of the keys in the json aren't as in this SO question). I then tried the elemMatch in the actual code (C# MongoDb.Driver) & no documents were returned.
• aneroid: 1. You said elemMatch worked but now for your Atlas docs with different field names, it doesn't work. So it's more likely that you've put the wrong expression or path. 2. To get only the matching grandkids, you'll need to do that in an aggregation pipeline and unwind and match again. 3. Ask a separate question with your actual data/Atlas data examples and field names and matching values, including getting just the matching grandkids as an aggregation/projection.
• aneroid: If that helped, read: What should I do when someone answers my question?, and about accepting and voting.
• Orion: I made a follow up post here : stackoverflow.com/questions/79566640/…