Elasticsearch. Using term aggregation, return values where doc count is less than some value

Question

I want to group values by field(account id in my case) using term aggregation and return only fields where doc_count is less than some value.

I can specify min_doc_count parameter, but there is no max_doc_count. So I'm looking for a way to simulate this behavior. One of my many tries is this, but it doesn't work.

{
  "size": 0,
  "aggs": {
    "by_account": {
      "terms": {
        "field": "accountId"
      },
      "aggs": {
        "by_account_filtered": {
          "bucket_selector": {
            "buckets_path": {
              "totalDocs": "_count"
            },
            "script": "params.totalDocs < 10000"
          }
        }
      }
    }
  }
}

What am I doing wrong?

Pierre Mallet · Accepted Answer · 2018-07-09 15:36:51Z

2

The bucket_selector aggregation need to be nested ( since its a parent-type aggregation ) and sibling of a metric aggregation that it will use to filter buckets.

So we use a top level terms aggregation, then use a nested value_count aggregation to expose the bucket doc_count to the sibling selector_bucket aggregation

try this :

{
  "size": 0,
  "aggs": {
    "by_account": {
      "terms": {
        "field": "accountId"
      },
      "aggs": {
        "by_account_number": {
          "value_count" : {
            "field" : "accountId"
          }
        },
        "by_account_filtered": {
          "bucket_selector": {
            "buckets_path": {
              "totalDocs": "by_account_number"
            },
            "script": "params.totalDocs < 10000"
          }
        }
      }
    }

  }
}

EDIT : If you want to get the lowest account doc_count

{
      "size": 0,
      "aggs": {
        "by_account": {
          "terms": {
            "field": "accountId",
            "order" : { "_count" : "asc" },
            "size": 100
          },
          "aggs": {
            "by_account_number": {
              "value_count" : {
                "field" : "accountId"
              }
            },
            "by_account_filtered": {
              "bucket_selector": {
                "buckets_path": {
                  "totalDocs": "by_account_number"
                },
                "script": "params.totalDocs < 10000"
              }
            }
          }
        }

      }
    }

edited Jul 9, 2018 at 15:36

answered Jul 9, 2018 at 13:42

Pierre Mallet

7,2512 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Artem Malinko Over a year ago

If I paste it as is, I get an error Found two aggregation type definitions in [by_account]: [terms] and [by_account_filtered]. If I add nested aggs and move by_account_filtered inside, I get No aggregation found for path [by_account._count].

Pierre Mallet Over a year ago

@ArtemMalinko sorry for the untested answer. This time it should be good, while a didnt have a script activated environnement to finish the testing.

Artem Malinko Over a year ago

It filters selected buckets, thank you very much for help. But it turned out that it's not exactly what I need. Probably I didn't formulate my question clear enough. This code filters top buckets returned by term query, while I want to filter all values stored in elastic. So I get nothing if all top results > 10000. I can increase the size of terms aggregation, but it doesn't scale well. Maybe you also know how to do this kind of aggregation? Like min_doc_count, but vice versa.

Pierre Mallet Over a year ago

it will be hard to get all the value and beside elasticsearch is not a regular database it make approximation on bucket count and other metrics.. but you could try reverse sort on doc_count with a fixed agg size to get the X lowest doc_count ( elastic.co/guide/en/elasticsearch/reference/current/… )

Randgalt Over a year ago

This answer helped me solve a hard problem. Thank you.

Collectives™ on Stack Overflow

Elasticsearch. Using term aggregation, return values where doc count is less than some value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related