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

Question

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.

Answer 1

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"
                                        ]
                                    }
                                }
                            ]
                        }
                    }
                }
            }