How to search on two filed of a single object in nested field simultaneously in elasticsearch

user_5088999 0
Viewed 867

I have a nested field in my elasticsearch. Imagine some data like this is stored on this field:

{
"id" : 1,
"students":[
     {"name": "John", "age": 20},{"name": "Alexander", "age":25},{"name": "Elizabeth", "age": 15}
    ]
},
"id" : 2,
"students":[
       {"name": "John", "age": 23},{"name": "Thomas", "age":30}
      ]
}

How can I query to get those documents which have student "John" and the his "age" is more than 21. If the query works well, the first document will not re retrieved as answer because "John" is 20 years old and we are looking for a "john" who is at least 21 years old. So, the second document must be retrieved. Thanks for your help.

elasticsearch
2 Answers

You need to use the nested query with inner_hits with source filtering to get the expected output in a better format.

Also please refer inner_hits so that you can do advance operations on your query and search result.

Index mapping, which includes the creation of .keyword fields so that you can retrieve the field-values for text fields

{
    "mappings": {
        "properties": {
            "id": {
                "type": "long"
            },
            "students": {
                "type" : "nested",
                "properties": {
                    "age": {
                        "type": "long"
                    },
                    "name": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    }
                }
            }
        }
    }
}

Index your sample docs as you have in question and search query to retrieve the nested document

{
    "query": {
        "nested": {
            "path": "students",
            "query": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "students.name": "John"
                            }
                        },
                        {
                            "range": {
                                "students.age": {
                                    "gt": 20
                                }
                            }
                        }
                    ]
                }
            },
            "inner_hits": {
                "_source": false,
                "docvalue_fields": [
                    {
                        "field": "students.name.keyword",
                        "format": "use_field_mapping"
                    }
                ]
            }
        }
    }
}

And inner-search result

"inner_hits": {
                    "students": {
                        "hits": {
                            "total": {
                                "value": 1,
                                "relation": "eq"
                            },
                            "max_score": 1.8754687,
                            "hits": [
                                {
                                    "_index": "nestedstudent",
                                    "_type": "_doc",
                                    "_id": "2",
                                    "_nested": {
                                        "field": "students",
                                        "offset": 0
                                    },
                                    "_score": 1.8754687, // note this
                                    "fields": {
                                        "students.name.keyword": [
                                            "John"
                                        ]
                                    }
                                }
                            ]
                        }
                    }
                }
            }
user_40394310

Adding a working example with index mapping,search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "students": {
        "type": "nested"
      }
    }
  }
}

Search Query:

{
  "query": {
    "nested": {
      "path": "students",
      "query": {
        "bool": {
          "must": [
            { "match": { "students.name": "John" } },
            { "range": { "students.age": { "gt": 21 } } }
          ]
        }
      }
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "64460956",
        "_type": "_doc",
        "_id": "2",
        "_score": 1.8754687,
        "_source": {
          "id": 2,
          "students": [
            {
              "name": "John",
              "age": 23
            },
            {
              "name": "Thomas",
              "age": 30
            }
          ]
        }
      }
    ]
user_103487580
Related