Terms are not working with multilevel nested query

[ravee1112]

Below is my mapping for index:

{
  "properties" : {
    "solutions" : {
      "type" : "nested",
      "properties" : {
        "deliverySkills" : {
          "type" : "nested",
          "properties" : {
            "mappingList" : {
              "type" : "text"
            },
            "name" : {
              "type" : "text"
            }
          }
        }
      }
    }
  }
}

Mine data for above mapping is:

{
  "solutions" : [
    {
      "deliverySkills" : [
        {
          "name" : "Python",
          "mappingList" : [ ]
        },
        {
          "name" : "JSON",
          "mappingList" : [ ]
        },
        {
          "name" : "API design",
          "mappingList" : [
            "Design API"
          ]
        }
      ]
    },
    {
      "deliverySkills" : [
        {
          "name" : "HTTP",
          "mappingList" : [ ]
        },
        {
          "name" : "HTTPS",
          "mappingList" : [ ]
        },
        {
          "name" : "JavaScript",
          "mappingList" : [ ]
        }
      ]
    }
  ]
  }

I tried with the below query:

{
  "query": {
    "nested": {
      "path": "solutions",
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "solutions.deliverySkills",
                "query": {
                  "terms": {
                    "solutions.deliverySkills.name": ["JSON", "Python"]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

But it's not working. When i tried with the match instead of terms than it is working but i need to use the terms as i have to check from the array

[RabBit_BR (andre.coelho)]

Hi!!

If you want to use the Terms Query the value must be exactly the same as the document.

Returns documents that contain one or more exact terms in a provided field.

The terms query is the same as the term query, except you can search for multiple values.

You need add in name field this:

    "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }

Your mapping:

{
  "mappings": {
    "properties": {
      "solutions": {
        "type": "nested",
        "properties": {
          "deliverySkills": {
            "type": "nested",
            "properties": {
              "mappingList": {
                "type": "text"
              },
              "name": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Query:

{
  "query": {
    "nested": {
      "path": "solutions",
      "query": {
        "bool": {
          "must": [
            {
              "nested": {
                "path": "solutions.deliverySkills",
                "query": {
                  "terms": {
                    "solutions.deliverySkills.name.keyword": ["JSON", "Python"]
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}

[ravee1112]

I tried using the above solution but the result is the same as i am getting no results. Also the values are exactly same as the document.

[RabBit_BR (andre.coelho)]

@RabBit_BR

"name": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword",
                    "ignore_above": 256
                  }
                }

You add this part in your mapping?

[ravee1112]

Yes, I have added this and tried but not working

[RabBit_BR (andre.coelho)]

Could you send the query that is being executed? I can test it here.

[Tomo_M (Tomohiro Mitani)]

I will give a little supplementary explanation.

First, if you have never read this warning:

WARNING: Avoid using the term query for text fields.
By default, Elasticsearch changes the values of text fields as part of analysis. This can make finding exact matches for text field values difficult.
To search text field values, use the match query instead.

please read this doc.

Text fields are analyzed (split for tokens, delete stop words, convert lower case... etc by default) to tokens before indexed but the phrase for term/terms query are not analyzed. The phrase must be exactly the same as the token after analysis. That's the reason why term/terms query doesn't hit the document.

Besides RabBit's suggestion to add keyword field, there are an option only to change the type itself from text to keyword just as:

"name": {
                "type": "keyword"
}

. To change the mappings, you need delete and create the index again or re-index from the index to another index.