Terms aggregation with filters inside

Elastic Stack Elasticsearch
1

Hi all,

I am trying to write a query to achieve this :

sample data :

{"code":"AAA", "bool1":true, "bool2":true, "bool3": false}
{"code":"AAA", "bool1":false, "bool2":true, "bool3": false}
{"code":"BBB", "bool1":true, "bool2":false, "bool3": true}
{"code":"BBB", "bool1":true, "bool2":true, "bool3": true}

expected result:

{
    "AAA": {
        "count_bool1": 1,
        "count_bool2": 2,
        "count_bool3": 0
    },
    "BBB": {
        "count_bool1": 2,
        "count_bool2": 1,
        "count_bool3": 2
    },
}

Is it possible using only one query ?

Thank you

2

Yep. The easiest way is if you do a terms aggregation on field "code", and then embed a terms agg for field "bool1" ,"bool2" and "bool3" inside that first agg, you'll get a count of true/false for each field, per code.

Something like:

{
  "aggregations": {
    "codes": {
      "terms": {
         "field": "code"
       },
       "aggs": {
         "bool1": {
            "terms": {
              "field": "bool1"
            }
          },
         "bool2": {
            "terms": {
              "field": "bool2"
            }
          },
         "bool3": {
            "terms": {
              "field": "bool3"
            }
          }
       }
    }
  }
}

You could also use a script to sum up true values:

{
  "aggregations": {
    "codes": {
      "terms": {
         "field": "code"
       },
       "aggs": {
         "bool1": {
            "sum": {
              "script": {
                 "inline": "doc.bool1.value.equals(true) ? 1 : 0;"
               }
            }
          },
         "bool2": {
            "sum": {
              "script": {
                 "inline": "doc.bool2.value.equals(true) ? 1 : 0;"
               }
            }
          },
         "bool3": {
            "sum": {
              "script": {
                 "inline": "doc.bool3.value.equals(true) ? 1 : 0;"
               }
            }
          }
       }
    }
  }
}

Note: I didn't test either of those queries, so I may have messed up the syntax slightly on accident :slight_smile:

3

Thank you !

The script query works like a charm.

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.