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?

I just ingested your example document, ran the query and the document matched. Are you using any special mappings? — llermaly
– llermaly, Commented Feb 27, 2021 at 2:16
In order for this to work you probably need to make recArrNested and recNested of type nested in your index mapping, otherwise it won't work. — Val
– Val, Commented Feb 27, 2021 at 5:42
I have forgot to create it, but it does not change (even with nested instead of object). — GlinesMome
– GlinesMome, Commented Feb 27, 2021 at 7:15

Jozef - Spatialized.io · Accepted Answer · 2021-02-27 11:38:34Z

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
}

Collectives™ on Stack Overflow

Filtering a nested field in ElasticSearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related