How to use elasticsearch facet query to groupby the result

Question

I have a json data in the below format

{
"ID": { "Color": "Black", "Product": "Car" },
"ID": { "Color": "Black", "Product": "Car" },
"ID": { "Color": "Black", "Product": "Van" },
"ID": { "Color": "Black", "Product": "Van" },
"ID": { "Color": "Ash", "Product": "Bike" }
}

I want to calculate the count of car and the corresponding color. I am using elasticsearch facet to do this.

My query

$http.post('http://localhost:9200/product/productinfoinfo/_search?size=5', { "aggregations": { "ProductInfo": { "terms": { "field": "product" } } }, "facets": { "ProductColor": { "terms": { "field": "Color", "size": 10 } } } })

I am getting the output like below

"facets": { "ProductColor": { "_type": "terms", "missing": 0, "total": 7115, "other": 1448, "terms": [ { "term": "Black", "count": 4 }, { "term": "Ash","count":1} }, 
"aggregations": { "ProductInfo": { "doc_count_error_upper_bound": 94, "sum_other_doc_count": 11414, "buckets": [ { "key": "Car", "doc_count": 2 }, { "key": "Van", "doc_count": 2 }, { "key": "Bike", "doc_count": 1 } ] } } } 

What I actually want is,

[ { "key": "Car", "doc_count": 2, "Color":"Black", "count":2 }, { "key": "Van", "doc_count": 2,"Color":"Black", "count":2 }, { "key": "Bike", "doc_count": 1,"Color":"Ash", "count":1 } ]

I would like to groupby the result . Is it possible to do it in elasticsearch query.

Thanks in advance

Answer 1

This is because you're using both aggregations and facets, which, if they are similar, are not meant to be used together.

Facets are deprecated and will be soon removed from ElasticSearch. Aggregations are the way to go to make "group by"-like queries.

You just have to nest another terms aggregation in the first one, like this :

{
  "aggs": {
    "By_type": {
      "terms": {
        "field": "Product"
      },
      "aggs": {
        "By_color": {
          "terms": {
            "field": "Color"
          }
        }
      }
    }
  }
}

And the result will be close to what you want :

"aggregations": {
      "By_type": {
         "doc_count_error_upper_bound": 0,
         "sum_other_doc_count": 0,
         "buckets": [
            {
               "key": "bike",
               "doc_count": 2,
               "By_color": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "ash",
                        "doc_count": 1
                     },
                     {
                        "key": "black",
                        "doc_count": 1
                     }
                  ]
               }
            },
            {
               "key": "car",
               "doc_count": 2,
               "By_color": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "black",
                        "doc_count": 2
                     }
                  ]
               }
            },
            {
               "key": "van",
               "doc_count": 1,
               "By_color": {
                  "doc_count_error_upper_bound": 0,
                  "sum_other_doc_count": 0,
                  "buckets": [
                     {
                        "key": "black",
                        "doc_count": 1
                     }
                  ]
               }
            }
         ]
      }
   }