Is it possible to calculate the number of results by grouping them by several criteria?

[Denys_Lukash]

Hi dear community.

I'm a new user of elastic and I'm having trouble building a query, I don't know if it's possible to do what I want. My task is to get the number of products in each category. The problem is that the grouping is done not by a single category id, but by a set of id's that belong to the one category.

Elastic document Product has indexed fields { /.../ "genericId": 123 /.../ }

From the database I get a list of categories, Category_1, Category_2. There are several genericId belong to each category.
Category_1 has generics 123, 124, 125
Category_2 has generics 126, 127, 128

As a result, I need to get the number of products in each category using a set of generics from each category to search.

I can't figure out how to do this with one query, I would be grateful for any help or advice. Thanks.

[Irina_Truong]

Hi @Denys_Lukash , welcome!

You would use a filter aggregations for this.

For example, if you have an index as follows:

PUT my-products
{
  "mappings": {
    "properties": {
      "name": {"type": "keyword"},
      "genericid": {"type": "short"}
    }
  }
}

with 3 documents:

POST my-products/_bulk
{ "index" : { "_id" : "1" } }
{ "name" : "Product 1", "genericid": [1, 2, 3] }
{ "index" : { "_id" : "2" } }
{ "name" : "Product 2", "genericid": [2, 3, 4] }
{ "index" : { "_id" : "3" } }
{ "name" : "Product 3", "genericid": [3, 4, 5] }

Here is how you could query that:

GET my-products/_search
{
  "size": 0, 
  "aggs": {
    "Category 1": {
      "filter": {
        "terms": {
          "genericid": [1, 2]
        }
      }
    },
    "Category 2": {
      "filter": {
        "terms": {
          "genericid": [3, 4, 5]
        }
      }
    }
  }
}

The response would be something like this:

{
  [...]
  "aggregations": {
    "Category 1": {
      "doc_count": 2
    },
    "Category 2": {
      "doc_count": 3
    }
  }
}

Documentation:

Filter aggregation | Elasticsearch Guide [8.1] | Elastic

I hope this helps.

[Denys_Lukash]

Hi Irina, looks pretty good and simple. just tried it for my case and it seems that all works as expected.

Thank you a lot