ElasticSearch nested query filter by second item in array

Question

I want to filter groups in nested filed stats, by second value (here I marked it as value_filtered). stats cointains of 4 objects. I want all groups with second item in array greater than 0 (value_filtered > 0). Below example of document:

        {
          "group_name": "Group_1",
          "date": "2020-05-20",
          "stats" : [
            {
              "name_en" : "xxx",
              "value" : 45
            },
            {
              "name_en" : "value_filtered",
              "value" : 0
            },
            {
              "name_en" : "vvv",
              "value" : 20
            },
            {
              "name_en" : "zzz",
              "value" : 0.666
            }
          ]
        }

My approach was to use filter script, however from some reasons it does not work. But the same script not in filter, but in scripting field works - below sample of my code:

query = {
    'size': 100,
    '_source': {
        'include': ['group_name']
    },
    "query": {
        "bool": {
            "filter": [
                {
                    'term': {
                        'date': '2020-05-20'
                    }
                }
            ]
        }
    },
    'script_fields': {
        'stats': {
            'script': {
                'lang': 'painless',
                'source': """
                int value_filtered = (int) params._source['metrics'][1].value;
                return value_filtered > 0;            
                """
            }
        }
    }
}

Answer 1

I generated some index with group and stats (for simplicity I removed date) just to test only script filter:

Mapping:

{
  "my_test_stats" : {
    "aliases" : { },
    "mappings" : {
      "properties" : {
        "group" : {
          "type" : "keyword"
        },
        "stats" : {
          "type" : "nested",
          "properties" : {
            "name_en" : {
              "type" : "keyword"
            },
            "value" : {
              "type" : "keyword"
            }
          }
        }
      }
    },
    "settings" : {
      ...
    }
  }
}

Three docs in my index:

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_test_stats",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "group" : "abc",
          "stats" : [
            {
              "name_en" : "xxx",
              "value" : 45
            },
            {
              "name_en" : "value_filtered",
              "value" : 0
            },
            {
              "name_en" : "vvv",
              "value" : 20
            },
            {
              "name_en" : "zzz",
              "value" : 0.666
            }
          ]
        }
      },
      {
        "_index" : "my_test_stats",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "group" : "xyzz",
          "stats" : [
            {
              "name_en" : "xxx",
              "value" : 45
            },
            {
              "name_en" : "value_filtered",
              "value" : 20
            },
            {
              "name_en" : "vvv",
              "value" : 20
            },
            {
              "name_en" : "zzz",
              "value" : 0.666
            }
          ]
        }
      },
      {
        "_index" : "my_test_stats",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "group" : "defg",
          "stats" : [
            {
              "name_en" : "xxx",
              "value" : 45
            },
            {
              "name_en" : "value_filtered",
              "value" : 30
            },
            {
              "name_en" : "vvv",
              "value" : 20
            },
            {
              "name_en" : "zzz",
              "value" : 0.666
            }
          ]
        }
      }
    ]
  }
}

Adjusted query:

GET /my_test_stats/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": {
              "source": """
                def stats_values = doc['stats.value'];
                if (stats_values.size() == 0 || stats_values.length < 2) {
                  return false;
                }
                int value_filtered = (int) stats_values[1]; 
                return value_filtered > 0;
              """
            }
          }
        }
      ]
    }
  }
}

Outcome is empty list, but it should be 2 records.

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

Answer 2

The script field & script filter Painless contexts are slightly different.

Here's what you're after:

{
  "size": 100,
  "_source": {
    "include": [
      "group_name"
    ]
  },
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "date": "2020-05-20"
          }
        },
        {
          "script": {
            "script": {
              "source": """
                def stats_values = doc['stats.value'];
                if (stats_values.size() == 0 || stats_values.length < 2) {
                  return false;
                }
                int value_filtered = (int) stats_values[1]; 
                return value_filtered > 0;
              """
            }
          }
        }
      ]
    }
  }
}