elasticsearch

ElasticSearch: Filter by minimal value in nested field


I have index (contestes users results) with structure like this:

{
  "contest_name",
  "contest_date",
  "user_scores": [
     "username": score,
     "username": score,
     "username": score,
  ],
},

user_scores is a nested field.

How I can filter contests that NO ONE user get less than 90 score in that contest?

Next task is to get count of such contests in agg.

p.s. I can't change index structure or use DB instead


Solution

  • Query:

    "query": {
        "bool": {
            "must": {
                "script": {
                    "script": {
                        "lang": "expression",
                        "source": "doc['user_scores'].min() >= 90"
                    }
                }
            }
        }
    }