# Facet and grouping

**URL:** <https://discuss.elastic.co/t/facet-and-grouping/8308>\
**Category:** Elasticsearch\
**Created:** [July 4, 2012, 9:45am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308 "2012-07-04T09:45:52Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![pric](https://avatars.discourse-cdn.com/v4/letter/p/97f17d/32.png) [@pric](https://discuss.elastic.co/u/pric)\
**Post date:** [July 4, 2012, 9:45am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/1 "2012-07-04T09:45:52Z")

</div>

Hello, I would like to know if it's possible to do this with elasticsearch:

my documents: (it's for the example, in the real there is more fields)

doc 1 -\> brand: audi, color: black  
doc 2 -\> brand: audi, color: white  
doc 3 -\> brand: audi, color: black  
doc 4 -\> brand: bmw, color: white

and the facets result I would like to get:

black -\> 1 (because just one brand, audi)  
white -\> 2 (because 2 brand, audi and bmw)

can I do this with ES. Thanks

---

<div class="post-metadata">

**Author:** ![Lukas\_Vlcek1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas_vlcek1/32/819_2.png) [@Lukas\_Vlcek1](https://discuss.elastic.co/u/Lukas_Vlcek1)\
**Post date:** [July 4, 2012, 10:49am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/2 "2012-07-04T10:49:22Z")

</div>

Hi,

I think you might want to look at Terms Stats facet

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

Regards,  
Lukas

On Wed, Jul 4, 2012 at 11:45 AM, pric [p.richard195@laposte.net](mailto:p.richard195@laposte.net) wrote:

> Hello, I would like to know if it's possible to do this with elasticsearch:
> 
> my documents: (it's for the example, in the real there is more fields)
> 
> doc 1 -\> brand: audi, color: black  
> doc 2 -\> brand: audi, color: white  
> doc 3 -\> brand: audi, color: black  
> doc 4 -\> brand: bmw, color: white
> 
> and the facets result I would like to get:
> 
> black -\> 1 (because just one brand, audi)  
> white -\> 2 (because 2 brand, audi and bmw)
> 
> can I do this with ES. Thanks

---

<div class="post-metadata">

**Author:** ![pric](https://avatars.discourse-cdn.com/v4/letter/p/97f17d/32.png) [@pric](https://discuss.elastic.co/u/pric)\
**Post date:** [July 4, 2012, 11:32am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/3 "2012-07-04T11:32:35Z")

</div>

Thanks for your help Lukáš but I can't do this with terms stats.

If somebody have another idea?  
thanks

---

<div class="post-metadata">

**Author:** ![Lukas\_Vlcek1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas_vlcek1/32/819_2.png) [@Lukas\_Vlcek1](https://discuss.elastic.co/u/Lukas_Vlcek1)\
**Post date:** [July 4, 2012, 3:08pm UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/4 "2012-07-04T15:08:18Z")

</div>

Yea, you are right. I think there is no specific facet for this calculation  
type now.

However, with a little bit more work you might be able to get this. But it  
depends on your particular use case. For example you could add a new filed  
into your object which would be the combination of 'color' and 'car maker'  
and then you could do the terms facet on this field and process it on the  
client side. But if you are going to have a lot of combinations this would  
not work well (because you will have to pull all the distinct value using  
all\_terms option

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

and  
then do the final sorting by aggregated counts on the client as well).

Regards,  
Lukas

On Wed, Jul 4, 2012 at 1:32 PM, pric [p.richard195@laposte.net](mailto:p.richard195@laposte.net) wrote:

> Thanks for your help Lukáš but I can't do this with terms stats.
> 
> If somebody have another idea?  
> thanks

---

<div class="post-metadata">

**Author:** ![pric](https://avatars.discourse-cdn.com/v4/letter/p/97f17d/32.png) [@pric](https://discuss.elastic.co/u/pric)\
**Post date:** [July 4, 2012, 6:46pm UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/5 "2012-07-04T18:46:39Z")

</div>

Thanks Lukáš, but in my case it will be not possible. I will try to fin  
another way.

---

<div class="post-metadata">

**Author:** ![sujoysett](https://avatars.discourse-cdn.com/v4/letter/s/2acd7d/32.png) [@sujoysett](https://discuss.elastic.co/u/sujoysett)\
**Post date:** [July 5, 2012, 5:55am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/6 "2012-07-05T05:55:53Z")

</div>

You can use a script facet like this, with separator of your choice (I  
prefer '~~~')

{  
"from": 0,  
"size": 0,  
"facets": {  
"test": {  
"terms": {  
"script": "doc['color'].value + "~~~" +  
doc['brand'].value"  
},  
"global": false  
}  
}  
}

It will give you facet results like

```
"terms": [
    {
        "term": "black~~~audi",
        "count": 2
    },
    {
        "term": "white~~~audi",
        "count": 1
    },
    {
        "term": "white~~~bmw",
        "count": 1
    }
]

```

Now you can to use a recursive program (I used a simple recursive  
javascript function here) to convert this json to some hierarchical json ,  
something like the following

{  
"black": {  
"audi": 2  
},  
"white": {  
"audi": 1,  
"bmw": 1  
}  
}

Counting the elements at the desired level would give you desired count.  
Such as 'white' has 2 elements, 'black' has 1 element, and such.

I faced the shortage of hierarchical facet in elasticsearch, and since it  
was much necessary for building json for advanced drill down  
visualizations, developed multilevel json (4-5 levels drill down) by this  
way.

-- Sujoy

---

<div class="post-metadata">

**Author:** ![Lukas\_Vlcek1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukas_vlcek1/32/819_2.png) [@Lukas\_Vlcek1](https://discuss.elastic.co/u/Lukas_Vlcek1)\
**Post date:** [July 5, 2012, 6:45am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/7 "2012-07-05T06:45:34Z")

</div>

Hi,

yea, this will work but only to the certain level of color brand  
combinations. Generally this would not be very efficient for higher number  
of combinations. The problematic part is order (sort) of aggregated data  
(which you need to calculate on the client side) because if you are after  
DESC like sort by count you would have to pull all the possible  
combinations. And if I understand it correctly then even if you want to get  
only top N items it still means you have to process all possible  
combinations.

Regards,  
Lukas

On Thu, Jul 5, 2012 at 7:55 AM, Sujoy Sett [sujoysett@gmail.com](mailto:sujoysett@gmail.com) wrote:

> You can use a script facet like this, with separator of your choice (I  
> prefer '~~~')
> 
> {  
> "from": 0,  
> "size": 0,  
> "facets": {  
> "test": {  
> "terms": {  
> "script": "doc['color'].value + "~~~" +  
> doc['brand'].value"  
> },  
> "global": false  
> }  
> }  
> }
> 
> It will give you facet results like
> 
> ```
> "terms": [
> {
> "term": "black~~~audi",
> "count": 2
> },
> {
> "term": "white~~~audi",
> "count": 1
> },
> {
> "term": "white~~~bmw",
> "count": 1
> }
> ]
> 
> ```
> 
> Now you can to use a recursive program (I used a simple recursive  
> javascript function here) to convert this json to some hierarchical json ,  
> something like the following
> 
> {  
> "black": {  
> "audi": 2  
> },  
> "white": {  
> "audi": 1,  
> "bmw": 1  
> }  
> }
> 
> Counting the elements at the desired level would give you desired count.  
> Such as 'white' has 2 elements, 'black' has 1 element, and such.
> 
> I faced the shortage of hierarchical facet in elasticsearch, and since it  
> was much necessary for building json for advanced drill down  
> visualizations, developed multilevel json (4-5 levels drill down) by this  
> way.
> 
> -- Sujoy

---

<div class="post-metadata">

**Author:** ![sujoysett](https://avatars.discourse-cdn.com/v4/letter/s/2acd7d/32.png) [@sujoysett](https://discuss.elastic.co/u/sujoysett)\
**Post date:** [July 5, 2012, 7:10am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/8 "2012-07-05T07:10:24Z")

</div>

Hi Lukáš,

Of course there are limitations. I have used this for five fields  
combinations max (means five levels drill down), on keyword analyzed  
fields, with an index of approx 1lac docs. Response time was acceptable  
with a normal UI hosting advanced charts.

For parsing, iterating and sorting requirements I used underscore.js  
utility. In absence of hierarchical faceting, this was the best I could get  
as a way-around, and it worked well. For finding 'top' results fast on huge  
datasets, separate query on each field can give 'top' of each fields, and  
using those to filter the final query can help.

But again, these are customized alternatives, to use only when primary is  
missing.

-- Sujoy.

On Thursday, July 5, 2012 12:15:34 PM UTC+5:30, Lukáš Vlček wrote:

> Hi,
> 
> yea, this will work but only to the certain level of color brand  
> combinations. Generally this would not be very efficient for higher number  
> of combinations. The problematic part is order (sort) of aggregated data  
> (which you need to calculate on the client side) because if you are after  
> DESC like sort by count you would have to pull all the possible  
> combinations. And if I understand it correctly then even if you want to get  
> only top N items it still means you have to process all possible  
> combinations.
> 
> Regards,  
> Lukas
> 
> On Thu, Jul 5, 2012 at 7:55 AM, Sujoy Sett wrote:
> 
> > You can use a script facet like this, with separator of your choice (I  
> > prefer '~~~')
> > 
> > {  
> > "from": 0,  
> > "size": 0,  
> > "facets": {  
> > "test": {  
> > "terms": {  
> > "script": "doc['color'].value + "~~~" +  
> > doc['brand'].value"  
> > },  
> > "global": false  
> > }  
> > }  
> > }
> > 
> > It will give you facet results like
> > 
> > ```
> > "terms": [
> > {
> > "term": "black~~~audi",
> > "count": 2
> > },
> > {
> > "term": "white~~~audi",
> > "count": 1
> > },
> > {
> > "term": "white~~~bmw",
> > "count": 1
> > }
> > ]
> > 
> > ```
> > 
> > Now you can to use a recursive program (I used a simple recursive  
> > javascript function here) to convert this json to some hierarchical json ,  
> > something like the following
> > 
> > {  
> > "black": {  
> > "audi": 2  
> > },  
> > "white": {  
> > "audi": 1,  
> > "bmw": 1  
> > }  
> > }
> > 
> > Counting the elements at the desired level would give you desired count.  
> > Such as 'white' has 2 elements, 'black' has 1 element, and such.
> > 
> > I faced the shortage of hierarchical facet in elasticsearch, and since it  
> > was much necessary for building json for advanced drill down  
> > visualizations, developed multilevel json (4-5 levels drill down) by this  
> > way.
> > 
> > -- Sujoy

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 3:21am UTC](https://discuss.elastic.co/t/facet-and-grouping/8308/9 "2017-07-06T03:21:25Z")

</div>