Finding a specific element in nested Array MongoDB

Question

DB Schema

[{
  "_id": 1,
  "name": "city1",
  "districts": [
    {
      "id": 5,
      "name": "district 1",
      "neighborhoods": [
        {
          "id": 309,
          "name": "neighborhood 1"
        }
      ]
    },
    {
      "id": 6,
      "name": "district 2",
      "neighborhoods": [
        {
          "id": 52280,
          "name": "neighborhood 2"
        }
      ]
    }
},
{
  "_id": 1,
  "name": "city2",
  "districts": [
    {
      "id": 5,
      "name": "district 3",
      "neighborhoods": [
        {
          "id": 309,
          "name": "neighborhood 3"
        }
      ]
    },
    {
      "id": 6,
      "name": "district 4",
      "neighborhoods": [
        {
          "id": 52280,
          "name": "neighborhood 4"
        },
        {
          "id": 52287,
          "name": "neighborhood 5"
        }
      ]
    }
}]

Goal

I would like to be able to check whether a 3-tuple combination is valid. Given some values for name, districts.name, and district.neighborhoods.name; I would like to check if those 3 values do indeed represent a valid combination. By valid I mean that they are nested in each other.

Example 1

If I am given city1, district 1, and neighborhood 1 as input, this is a valid combination (Because they are nested inside each other)

Example 2

If I am given city2, district 4, and neighborhood 4 as input, this is a valid combination

Example 3

If I am given city1, district 1, and neighborhood 2 as input, this is NOT a valid combination (because they are not nested inside each other)

Example 4

If I am given city1, district 3, and neighborhood 3 as input, this is NOT a valid combination

Expected Output

Assuming I am given city1, district 1, and neighborhood 1 (a valid combination) for name, districts.name, and districts.neighborhoods.name respectively. The output should be:

{
  "_id": 1,
  "name": "city1",
  "districts": [
    {
      "id": 5,
      "name": "district 1",
      "neighborhoods": [
        {
          "id": 309,
          "name": "neighborhood 1"
        }
      ]
    }
}

Assuming I am given city1, district 1, and neighborhood 2 (NOT a valid combination) for name, districts.name, and districts.neighborhoods.name respectively. The output should be empty or null or something indicating that an array element with these values does not exist.

Current Approach

doesLocationExist = await locationDbConnection.find_one(
        { "location" : {
            "$elemMatch" :  
                {"name" : city, "districts.name" : district, "districts.neighborhoods.name": neighborhood}
            }
        }
    )

I was hoping this would return a document if the combination is valid, but it always returns None even when the combination is valid. Essentially what I am trying to do is retrieve a double-nested array element using the "path" (the 3-tuple input mentioned earlier); if that element exists, that means it is a valid combination, otherwise, it is not.

Previous Questions That Did Not Help

P.S: the neighborhood and district names are not unique, so I cannot use projection to display only the array element of interest

What is the output you are expecting? Can you edit your posted question? — Kayvan Shah, Mar 27 '23 at 09:19
Also, the sample you have shared is an invalid JSON, kindly update the correct one. — Kayvan Shah, Mar 27 '23 at 09:24
can there be situations that the district array and neighborhood array can have more than 1 in final — cmgchess, Mar 27 '23 at 11:09
@cmgchess, is there not a more efficient way than unwinding everything? Because my arrays are 1000s of elements. And no, the final document is always a unique city-district-neighborhood combination — Ahmet-Salman, Mar 27 '23 at 12:31
2 unwinds and a match should the job easily then. the problem without unwinding is that you get the unwanted elements in district and neighbour arrays — cmgchess, Mar 27 '23 at 12:33
@cmgchess I don't need the output, so to speak, but I only need to know whether the city-district-neighborhood combination is valid. In other words, I want to know whether the provided district exists in the provided city, and the provided neighborhood exists in the provided district. — Ahmet-Salman, Mar 27 '23 at 12:42
how about a nested elemMatch. this however will give the whole record if the correct match occurs but still you can get if the combination exists if there is a result — cmgchess, Mar 27 '23 at 13:09

score 1 · Accepted Answer · answered Mar 27 '23 at 13:20

A nested $elemMatch to check if the given combination exists. It will give a result if it does.

db.collection.aggregate([
  {
    $match: {
      name: city,
      districts: {
        $elemMatch: {
          name: district,
          neighborhoods: {
            $elemMatch: {
              name: neighborhood
            }
          }
        }
      }
    }
  }
])

playground

if you actually want the correct result you can keep doing $match,$unwind until you get the result. This is assuming if there is only 1 unique combination of city,district and neighborhood

db.collection.aggregate([
  { $match: { name: city } },
  { $unwind: "$districts" },
  { $match: { "districts.name": district } },
  { $unwind: "$districts.neighborhoods" },
  { $match: { "districts.neighborhoods.name": neighborhood } }
])