0

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;            
                """
            }
        }
    }
}
Konrad Marzec
  • 109
  • 1
  • 10

2 Answers2

1

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;
              """
            }
          }
        }
      ]
    }
  }
}
Joe - GMapsBook.com
  • 15,787
  • 4
  • 23
  • 68
  • I tried your solution and unfortunately it does not work. Got strange reason: ```"A document doesn't have a value for a field! Use doc[].size()==0 to check if a document is missing a field!"``` – Konrad Marzec May 20 '20 at 23:08
  • Yup -- gotta perform some checks in the script. I've updated my answer. – Joe - GMapsBook.com May 21 '20 at 09:28
  • Feel free to wrap everything in a try/catch too if your docs have inconsistent formats. – Joe - GMapsBook.com May 21 '20 at 09:37
  • Hi, now it seems like scripts does not throw error, but outcome is empty every time. Details of my case below. Thank you for helping. – Konrad Marzec May 21 '20 at 09:54
  • Oh I didn't know your mapping was actually `nested`. You're either gonna have to stick to `scripted_fields`, or follow this thread https://stackoverflow.com/a/40098750/8160318 or adjust your pipeline to this https://stackoverflow.com/a/56937799/8160318 and possibly this https://stackoverflow.com/a/56948351/8160318 – Joe - GMapsBook.com May 21 '20 at 10:23
0

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" : [ ]
  }
}
Konrad Marzec
  • 109
  • 1
  • 10