ElasticSearch find indexed objects with distinct nested list elements from specified terms

Question

Let's suppose we have a student with a (nested) list of scores as follows:

public class Student
{
    public string FullName { get; set; }
    public List<Score> Scores { get; set; } = new List<int>();
}

public class Score
{
    public int Value { get; set; }
}

How do I write an ElasticSearch query using NEST (or simple query) to get all students who have at least 2 different scores from 7 to 10.

Thus for example, if a student got scores {2,7,10} or {8,10}, he should be in our result, whereas a student with {10, 6, 5} or {8, 8} or {2, 7} should not get in our result.

What I have come up with is:

GET /index/student/_search
{
  "query": {
    "nested": {
      "path": "scores",
      "query": {
        "bool": {
          "should":
          [
            {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "scores.value": [7, 10] 
                    }
                  },
                  {
                    "terms":{
                      "scores.value": [8, 9]
                    }
                  }
                ]
              }
            },
            {
              "bool": {
                "filter": [
                  {
                    "terms": {
                      "scores.value": [7, 8] 
                    }
                  },
                  {
                    "terms":{
                      "scores.value": [9, 10]
                    }
                  }
                ]
              }
            }
          ]
        }
      }
    }
  }
}

But ElasticSearch does not seem to allow conjunctions of TERMS queries (returning 0 hits). And even if it did work, I would still require something more elegant, because in case we have more than 4 allowed values, this would become a pain.

UPDATE

I've tried the following script, but got 0 results as well:

GET /index/student/_search
{
  "query": {
    "nested": {
      "path": "scores",
      "query": {
        "bool": {
          "filter": [
            {
              "exists": {
                "field": "scores"
              }
            },
            {
              "script": {
                "script": """
                   boolean condition = false;
                   def availableScores = [7, 8, 9, 10];
                   def scores = doc['scores.id'].values;
                   
                   for (int i = 0; i < scores.length; i++)
                    for(int j = 0; j < availableScores.length; j++)
                      if (scores[i] == availableScores[j])
                      {
                        if (condition == true) 
                          return (true);
                          
                        condition = true;
                        availableScores.remove(j);
                        break;
                      } 
                   return (false)"""
              }
            }
          ]
        }
      }
    }
  }
}

Answer 1

After a long while, I have found a query that works:

GET /index/student/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "nested": {
                  "path": "scores",
                  "query": {
                    "terms": {
                      "scores.value": [
                        7,
                        10
                      ]
                    }
                  }
                }
              },
              {
                "nested": {
                  "path": "scores",
                  "query": {
                    "terms": {
                      "scores.value": [
                        8,
                        9
                      ]
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "nested": {
                  "path": "scores",
                  "query": {
                    "terms": {
                      "scores.value": [
                        7,
                        9
                      ]
                    }
                  }
                }
              },
              {
                "nested": {
                  "path": "scores",
                  "query": {
                    "terms": {
                      "scores.value": [
                        8,
                        10
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

The trick here was separating 1 nested query into multiple ones and putting them inside a should query.

I would still like a more elegant solution (through a script, I guess), but for now, I'll leave it as the final answer.