Filtering on multiple fields from nested object with Elasticsearch

Question

I am new to Elasticsearch and I'm trying to write some queries, which happen not to work. I have information about people and I want to query the people based on some attributes.

Here's a part of the mapping in the Elasticsearch:

"properties": {
    "doc": {
        ...
        "languages": [{
                "language": {
                    "type": "text",
                    "null_value": "NULL"
                },
                "level": {
                    "type": "integer",
                    "null_value": "NULL"
                }
            }],
         ...
    }
}

For example, I want to filter out all of the people who speak English with level greater than 3 and Japanese with level greater than 2.

What I've already tried is this approach that doesn't give the expected result.

"size": 1000,
"from": 0,
"query": {
  "bool": {
    "should": [
      {
        "bool": {
          "must": [
            {"match": {"languages.language": "english"}},
            {"range": {"languages.search_value": {"gt": 3}}}
          ]
        }
      },
      {
        "bool": {
          "must": [
            {"match": {"languages.language": "japanese"}},
            {"range": {"languages.search_value": {"gt": 2}}}
          ]
        }
      }
    ]
  }
}

The above query returns all of the people who speak English and Japanese, but the levels of the languages don't match. There are people who speak English with level 4 (which is ok), but Japanese with level 1 (which is not ok) and German with level 5. I guess the problem is that instead of looking for the wanted level just for the specified languages, my query looks for levels in all of the spoken languages by the people.

So, the result that I'm expecting from the query is to get all of the people who speak English with level 4 or 5 and Japanese with level 3, 4 or 5 (both in the same time). I don't care about the other languages they speak and their levels.

Any help or idea how can I solve this is welcomed.

jaspreet chahal · Accepted Answer · 2019-09-03 05:13:26Z

3

You need to use nested mapping for languages and level.

Mapping

PUT /employee
{
  "mappings": {
    "properties": {
      "name": {
        "type": "text"
      },
      "languages": {
        "type": "nested",
        "properties": {
          "language": {
            "type": "text"
          },
          "level": {
            "type": "integer"
          }
        }
      }
    }
  }
}

Data:

 [
      {
        "_index" : "employee",
        "_type" : "_doc",
        "_id" : "vkVy9WwBecJvLwMRPgIH",
        "_score" : 1.0,
        "_source" : {
          "name" : "User1",
          "languages" : [
            {
              "language" : "English",
              "level" : 3
            },
            {
              "language" : "Japanese",
              "level" : 2
            }
          ]
        }
      },
      {
        "_index" : "employee",
        "_type" : "_doc",
        "_id" : "v0Vy9WwBecJvLwMRagL6",
        "_score" : 1.0,
        "_source" : {
          "name" : "User2",
          "languages" : [
            {
              "language" : "English",
              "level" : 3
            },
            {
              "language" : "Japanese",
              "level" : 1
            }
          ]
        }
      },
      {
        "_index" : "employee",
        "_type" : "_doc",
        "_id" : "wEVy9WwBecJvLwMRlQIs",
        "_score" : 1.0,
        "_source" : {
          "name" : "User3",
          "languages" : [
            {
              "language" : "English",
              "level" : 3
            },
            {
              "language" : "Spanish",
              "level" : 1
            }
          ]
        }
      },
      {
        "_index" : "employee",
        "_type" : "_doc",
        "_id" : "wUWD9WwBecJvLwMRWgIq",
        "_score" : 1.0,
        "_source" : {
          "name" : "User4",
          "languages" : [
            {
              "language" : "English",
              "level" : 2
            },
            {
              "language" : "French",
              "level" : 1
            }
          ]
        }
      }
    ]

Query

GET employee/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "languages",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "languages.language": "English"
                    }
                  },
                  {
                    "range": {
                      "languages.level": {
                        "gte": 3
                      }
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "languages",
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "languages.language": "Japanese"
                    }
                  },
                  {
                    "range": {
                      "languages.level": {
                        "gte": 2
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Result:

 [
      {
        "_index" : "employee",
        "_type" : "_doc",
        "_id" : "vkVy9WwBecJvLwMRPgIH",
        "_score" : 3.974081,
        "_source" : {
          "name" : "User1",
          "languages" : [
            {
              "language" : "English",
              "level" : 3
            },
            {
              "language" : "Japanese",
              "level" : 2
            }
          ]
        }
      }
    ]

Solution is quite trivial , you need to understand nested type. Link in this answer has explanation for this

answered Sep 3, 2019 at 5:13

jaspreet chahal

9,1392 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Antonio Over a year ago

Ok, so I've made the needed changes in the mapping, but when I execute the query I get the following exception: "type": "illegal_state_exception", "reason": "[nested] nested object under path [languages] is not of nested type"

jaspreet chahal Over a year ago

As per exception mapping doesn't look to be updated. Can you paste your mapping and query

Antonio Over a year ago

"languages": {     "type": "nested",     "properties": {         "language": {                         "type": "text",                         "null_value": "NULL"          },          "level": {                         "type": "integer",                         "null_value": "NULL"           } }

The query is the same as yours

jaspreet chahal Over a year ago

"null_value": "NULL" is not allowed on type text and integer(must be giving you error). I tried by changing type to keyword wherever "null_value": "NULL" is specified and tried the query , it is returning data. Try GET <index_name>/_mapping it should give the mapping and verify if mapping is getting updated

Antonio Over a year ago

Yup, I've had a problem with the mapping (errors that haven't been shown), but finally I solved them. Thank you for your help!

Collectives™ on Stack Overflow

Filtering on multiple fields from nested object with Elasticsearch

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related