CODEWITHSUNDEEP
sortingelasticsearch
Akarsh Cholaveti
Akarsh Cholaveti

Reputation: 3

Sort documents based on matched inner nested objects in Elasticsearch

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!!

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.

Upvotes: 0

Views: 16

Answers (1)

imotov
imotov

Reputation: 30163

First of all I think you have wrong mapping. For this to work, the "Scores" field should be declared as nested. In order to filter by the score you need to use nested query. And finally in order to sort by that score you need to use nested sort with the same filter that you used in the nested query. If you combine all this together you will get something like this:

PUT test
{
  "settings": {
    "number_of_replicas": 0,
    "number_of_shards": 1
  },
  "mappings": {
    "properties": {
      "studentId": {
        "type": "long"
      },
      "studentName": {
        "type": "keyword"
      },
      "year": {
        "type": "long"
      },
      "Scores": {
        "type": "nested",
        "properties": {
          "subject": {
            "type": "keyword"
          },
          "score": {
            "type": "long"
          }

        }
      }
    }
  }
}

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

POST test/_search
{
  "size": 10000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "year": "2023"
          }
        },
        {
          "nested": {
            "path": "Scores",
            "query": {
              "range": {
                "Scores.score": {
                  "lte": 200
                }
              }
            }
          }
        }
      ]
    }
  },
  "sort": [
    {
      "Scores.score": {
        "mode": "max",
        "order": "asc",
        "nested": {
          "path": "Scores",
          "filter": {
            "range": {
              "Scores.score": {
                "lte": 200
              }
            }
          }
        }
      }
    }
  ]
}

Upvotes: 0

Related Questions