Finding the record for the maximum attempt

[ankitshah692 (Ankit Shah)]

Hello Experts ,
I need some help to formulate a query. Below is my index and here is what i need to do

"Find persons who has scoreTotal field matching to 300 from their latest attempt

This means , if i were to search 300 into this index, i should only get "Minnie" in the response. Because she is the only one who has 300 score in her latest attempt.

Currently my query gives me Minnie and Naomie. But i am interested only in Minnie. How do i make sure i always check the scoreTotal for each of the person from their latest attempt ?

Appreciate your help in this .

{
    "games": {
        "game": "pingPong",
        "person": [
            {
                "personName": "Dixie",
                "score": [
                    {
                        "scoreTotal": 500,
                        "attempt": 1
                    },
                    {
                        "scoreTotal": 200,
                        "attempt": 2
                    }
                ]
            },
            {
                "personName": "Minnie",
                "score": [
                    {
                        "scoreTotal": 876,
                        "attempt": 1
                    },
                    {
                        "scoreTotal": 300,
                        "attempt": 2
                    }
                ]
            },
            {
                "personName": "Naomi",
                "score": [
                    {
                        "scoreTotal": 300,
                        "attempt": 1
                    },
                    {
                        "scoreTotal": 400,
                        "attempt": 2
                    },
                    {
                        "scoreTotal": 500,
                        "attempt": 3
                    }
                ]
            }
        ]
    }
}

Here is my query for searching -

{
    "query": {
        "nested": {
            "path": "person",
            "query": {
                "nested": {
                    "path": "person.score",
                    "query": {
                        "bool": {
                            "must": [
                                {
                                    "match": {
                                        "person.score.scoreTotal": "300"
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        }
    }
}

[warkolm (Mark Walkom)]

I'd think a top hits agg, sorting on the attempt, would get what you want?

[ankitshah692 (Ankit Shah)]

Thanks for your response. Could you please provide an example of how the query would look like. The problem i was facing putting a sort inside the nested "Score". Also there could be other filters applied with this query. Like personName filter AND our score query.

Appreciate your help. I am sorry if i am asking something very easy. I am very rookie in this.