Filtering a nested field in ElasticSearch

Question

I am trying to filter some documents on ElasticSearch 7.11.

My index has the following mapping:

{
  "properties": {
    "recArrNested": {
      "type": "nested",
      "properties": {
        "nBTxt": {
          "type": "keyword"
        },
        "nBInt": {
          "type": "long"
        }
      }
    },
    "recNested": {
      "type": "object",
      "properties": {
        "nAInt": {
          "type": "long"
        },
        "nATxt": {
          "type": "keyword"
        }
      }
    },
    "recId": {
      "type": "keyword"
    }
  }
}

I have records which looks like that:

{
  "recArrNested": [
    {
      "nBTxt": "juliette",
      "nBInt": 10
    },
    {
      "nBTxt": "alpha",
      "nBInt": 42
    },
    {
      "nBTxt": "kilo",
      "nBInt": 11
    }
  ],
  "recNested": {
    "nAInt": 1,
    "nATxt": "manual"
  },
  "recId": "1alpha"
}

My goal is to filter the records which have a recArrNested.nBTxt equals to its recNested.nAInt NATO corresponding phonetic alphabet (alpha -> 1, bravo -> 2, and so on).

I have generated the following query:

{
  "size": 5,
  "from": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "recNested.nAInt": {
                          "value": "1"
                        }
                      }
                    },
                    {
                      "term": {
                        "recArrNested.nBTxt": {
                          "value": "alpha"
                        }
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "recNested.nAInt": {
                          "value": "2"
                        }
                      }
                    },
                    {
                      "term": {
                        "recArrNested.nBTxt": {
                          "value": "bravo"
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "sort": [
    {
      "recId": {
        "order": "desc"
      }
    }
  ],
  "track_scores": false
}

Sadly the above example document does not match. Do you have any suggestion to tackle that query properly?

Answer 1

I don't see a reason for declaring recNested to be of the type nested -- it'll suffice to keep recArrNested actually nested because you're dealing with arrays of objects that would've otherwise been flattened.

In accordance with your current mapping you'll want to use nested queries whenever applicable:

{
  "size": 5,
  "from": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "recNested.nAInt": {
                          "value": "1"
                        }
                      }
                    },
                    {
                      "nested": {
                        "path": "recArrNested",        <--
                        "query": {
                          "term": {
                            "recArrNested.nBTxt": {
                              "value": "alpha"
                            }
                          }
                        }
                      }
                    }
                  ]
                }
              },
              {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "recNested.nAInt": {
                          "value": "2"
                        }
                      }
                    },
                    {
                      "nested": {
                        "path": "recArrNested",        <--
                        "query": {
                          "term": {
                            "recArrNested.nBTxt": {
                              "value": "bravo"
                            }
                          }
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "sort": [
    {
      "recId": {
        "order": "desc"
      }
    }
  ],
  "track_scores": false
}