Search query based of number of values a field contains

[bevano (Bevan)]

Hi,

I was wondering whether you can point me at any documentation, or let me know if this is not possible for the following.

I am trying to write a query of a fields called result_code. Some of my documents contain multiple values for result_code
EXAMPLE: result_code: 00, 08, 51

And majority of my other documents result_code only has one value
EXAMPLE: result_code: 00

Is there any way I can filter out (ignore) documents where this field has multiple values. I want to do aggregations on the result_code where is only contains 1 value

Thanks

[vincenbr (Vincent Bréhin)]

Hi Bevano,
You could use a scripted filter aggregation in order to filter only single-value docs for the result_code field in your subsequent aggregations.
example:

GET myindex/_search
{
  "aggs": {
    "only_single_value": {
      "filter": {
        "script": {
          "script": {
            "source": "doc['result_code.keyword'].size() == 1",
            "lang": "painless"
          }
        }
      },
      "aggs": {
        "my_codes": {
          "terms": {
            "field": "result_code.keyword",
            "size": 10
          }
        }
      }
    }
  }
}

Otherwise (less flexible but more efficient at search time), if you don't do arithlmetics on result_code field, map it as type "text" and add a multi-field "lenght" of type "token_count".
Then, index the multiple values as a single concatenated value.
Upon search, you will be able to filter on the numeric field result_code.length=1

Hope this will help you !

Vincent

[bevano (Bevan)]

@vincenbr

{
  "aggs": {
    "only_single_value": {
      "filter": {
        "script": {
          "script": {
            "source": "doc['result_code.keyword'].size() == 1",
            "lang": "painless"
          }
        }
      },
      "aggs": {
        "my_codes": {
          "terms": {
            "field": "result_code.keyword",
            "size": 10
          }
        }
      }
    }
  }
}

This is perfect @vincenbr. I totally forgot about scripts since I rarely use them. Life saver!