If I had a data structure that looked like this

[{"_id" 1 
 "scores" [{"student_id": 1, "score": 100"}, {"student_id": 2, "score": 80"}
 ]}, 
{"_id" 2
 "scores" [{"student_id": 1, "score": 20"}, {"student_id": 2, "score": 90"}
 ]}]

Would it be possible to sort this dataset by student_1's score or by student_2's score?

For example if I sorted descending by student 1's score, I would get document 1,2, but if I sorted descending by student 2's score, I would get 2,1.

I could re-arrange the data, but I don't want to use another index because there's a bunch of metadata not included above for brevity. Thanks!

CodePudding user response：

Yes, it is possible. You must use "nested" field type for your scores, that way you can keep the relation between each student_id and its score.

You can read an article I wrote about that subject: https://opster.com/guides/elasticsearch/data-architecture/elasticsearch-nested-field-object-field/

Now the example:

Mappings

PUT test_students
{
  "mappings": {
    "properties": {
      "scores": {
        "type": "nested",
        "properties": {
          "student_id": {
            "type": "keyword"
          },
          "score": {
            "type": "long"
          }
        }
      }
    }
  }
}

Documents

PUT test_students/_doc/1
{
  "scores": [{"student_id": 1, "score": 100}, {"student_id": 2, "score": 80}]
}

PUT test_students/_doc/2
{
  "scores": [{"student_id": 1, "score": 20}, {"student_id": 2, "score": 90}]
}

Query

POST test_students/_search
{
   "sort" : [
       {
          "scores.score" : {
             "mode" :  "max",
             "order" : "desc",
             "nested": {
                "path": "scores",
                "filter": {
                   "term" : { "scores.student_id" : "2" }
                }
             }
          }
       }
    ]
}