How do I search an array that's nested in an array of objects in Elastic?

Question

I have an Elastic index that contains objects structured like this:

{
  dogs: [
    {
      name: 'wiener dog',
      id: 2,
      cats: [
        {
          name: 'mean cat',
          id: 5,
        },
        ...
      ],
    },
    ...
  ],
  ...
}

My question is: How do I search against this index for all documents that include a particular id in cats? A single match is fine.

What I have tried: I have tried many different queries, including nesting on dogs, and nesting on both dogs and cats. I have tried accessing the property directly via dogs.cats.id, and all combinations of the above. Here is an example in NEST:

query &= mst.Nested(n => n
    .Path("dogs")
    .Query(q => q
        .Nested(n => n
            .Path("dogs.cats")
            .Query(q => q
                .Terms(t => t
                    .Field("dogs.cats.id")
                    .Terms(catIds.ToList())
                )
            )
        )
    )
);

I have also tried with a single Nested with Field set to cats.id with no luck.

Any help here would be greatly appreciated. Changing the data structure at this point would be a much larger effort, and would be avoided if possible. Thanks!

Answer 1

From your information, I assume that the use of NestedQuery is ideal.

PUT bug_reports
{
  "mappings": {
    "properties": {
      "dogs": {
        "type": "nested",
        "properties": {
          "cats": {
            "type": "nested"
          }
        }
      }
    }
  }
}

POST bug_reports/_doc/1
{
   "dogs": [
    {
      "name": "wiener dog",
      "id": 1,
      "cats": [
        {
          "name":"red cat",
          "id": 4
        },
        {
          "name":"mean cat",
          "id": 5
        }
      ]
    }
  ]
}


POST bug_reports/_doc/2
{
   "dogs": [
    {
      "name": "none dog",
      "id": 2,
      "cats": [
        {
          "name":"mean cat",
          "id": 5
        }
      ]
    }
  ]
}

GET bug_reports/_search?filter_path=hits.hits
{
  "query": {
    "nested": {
      "path": "dogs",
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "dogs.cats",
                "query": {
                  "terms": {
                    "dogs.cats.id": [
                      4
                    ]
                  }
                }
              }
            },
            {
              "nested": {
                "path": "dogs.cats",
                "query": {
                  "terms": {
                    "dogs.cats.id": [
                      5
                    ]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}