Sort documents based on matched inner nested objects

Hello,

I am working on a sort query for my documents in the index. Here are the documents look like:

{
    "studentId": "123",
    "studentName": "Frodo",
    "year": "2023",
    "Scores": [{
            "subject": "Physics",
            "score": "100"
        },
        {
            "subject": "Chemistry",
            "score": "700"
        }
    ]
}
{
    "studentId": "456",
    "studentName": "Samwise",
    "year": "2023",
    "Scores": [{
            "subject": "Physics",
            "score": "200"
        },
        {
            "subject": "Chemistry",
            "score": "600"
        }
    ]
}
{
    "studentId": "789",
    "studentName": "Merry",
    "year": "2023",
    "Scores": [{
            "subject": "Physics",
            "score": "300"
        },
        {
            "subject": "Chemistry",
            "score": "500"
        }
    ]
}

I am trying to get results in descending order that falls in the range. For example, I am running following query

{
    "size": 10000,
    "query": {
        "bool": {
            "must": [{
                    "term": {
                        "year": "2023"
                    }
                },
                {
                    "range": {
                        "Scores.score": {
                            "lte": 200
                        }
                    }
                }
            ]
        }
    },
    "sort": [{
        "Scores.score": {
            "order": "desc"
        }
    }]
}

with the above query, I am getting the 2 docs as expected but I am getting doc with '100' score first instead of '200'. I think when ES is performing the sort, it is picking up 700 first and 600 next and giving the documents in that order. How can I do the query that sorts only on matched inner fields? In this case, the sorting should consider only 200, 100 scores and ignore others. Any help would be appreciated. Thanks!!

Hi @akarsh_cholaveti

Did you try to use Sort Mode Option?.

@RabBit_BR Thanks for your response. I have tried the sort mode option with min mode. It works with this particular example but it doesn't work with different scenario. For example, if I use the score as 800 in the same query with min mode, I am getting doc3 (score 300 and 500) as response but I expect doc1(score 100 and 700) since 700 is highest score.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.