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

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!

for more background depth, the kid objects are defined in code (C# using the MongoDB.Driver), & the “_id” field in the class is just a string, with the BsonId decorator. Thus, the actual _id field in the db is unique per parent, but not (necessarily) unique across parents.

I tried working out a query in MongoDb Playground, but it didn’t work, as expected. However, it revealed potentially the cause of the problem with the following error:

Invalid query:

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

I also looked for a way to convert the ObjectId ot a string & compare to the string in the query, but that didn’t work either.

Do I have to update the database to use the ObjectId/BsonId correctly before being able to query like I would like (ie, not comparing to an ObjectId/BsonId, but a separate field/property)?

someone else told me I can use elemMatch for this scenario, & it worked in MongoDb Playground! But when I tried the same query using the “real” json structure of a document in the Atlas Web UI & in the C# code, the query returns 0 documents. Here’s the query they gave me:

{
  kids: {
    $elemMatch: {
      _id: "Test1",
      "grandkids.name": "Bloh"
    }
  }
}