Bool query x Script filter

[Paulo_Silva]

Hi all,

I've come across with this situation here in my project. By running the following query:

GET /unittesttg1_tg1_fq1/_search
{
  "size": 0,
  "aggs": {
    "groupby": {
      "date_histogram": {
        "field": "LAST_MODIFIED_DATE",
        "interval": "month",
        "format": "yyyyMM",
        "min_doc_count": 1,
        "time_zone": "+08:00"
      },
      "aggs": {
        "measure": {
          "sum": {
            "field": "ENUMID"
          }
        }
      }
    }
  },
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "range": {
                  "LAST_MODIFIED_DATE": {
                    "gte": "2017-01-01T00:00:00",
                    "lte": "2017-01-31T23:59:59",
                    "time_zone": "+08:00"
                  }
                }
              },
              {
                "range": {
                  "LAST_MODIFIED_DATE": {
                    "gte": "2017-02-01T00:00:00",
                    "lte": "2017-02-28T23:59:59",
                    "time_zone": "+08:00"
                  }
                }
              }
            ],
            "filter": [
              {
                "script": {
                  "script": {
                    "inline": " (ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc['LAST_MODIFIED_DATE'].value), ZoneId.of('+08:00')).getHour() == 8)  || (ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc['LAST_MODIFIED_DATE'].value), ZoneId.of('+08:00')).getHour() == 9) "
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

I'm getting the months aggregated in and out the interval (I'm supposed to filter just the months "2017-01" and "2017-02"). For instance, the aggregated value of "2017-03" and "2017-04" are being shown.

However, if I remove the scripting filter bit (below) the monthly interval works all right

 "filter": [
                  {
                    "script": {
                      "script": {
                        "inline": " (ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc['LAST_MODIFIED_DATE'].value), ZoneId.of('+08:00')).getHour() == 8)  || (ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc['LAST_MODIFIED_DATE'].value), ZoneId.of('+08:00')).getHour() == 9) "
                      }
                    }
                  }
                ]

But of course, I lose the script filter I need.
Any thoughts?

Thanks
'

[spinscale (Alexander Reelsen)]

can you quickly explain what your goal here is? I dont think that the script filter is a great idea, because it requires processing of every single hit, but I want to understand the use-case first.

Thanks!