# How can i achieve this result using facet?

**URL:** <https://discuss.elastic.co/t/how-can-i-achieve-this-result-using-facet/14850>\
**Category:** Elasticsearch\
**Created:** [December 13, 2013, 6:45am UTC](https://discuss.elastic.co/t/how-can-i-achieve-this-result-using-facet/14850 "2013-12-13T06:45:48Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![abhiage](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@abhiage](https://discuss.elastic.co/u/abhiage)\
**Post date:** [December 13, 2013, 6:45am UTC](https://discuss.elastic.co/t/how-can-i-achieve-this-result-using-facet/14850/1 "2013-12-13T06:45:48Z")

</div>

data is

Action : "added" Product\_ID : 123  
Action : "added" Product\_ID : 124  
Action : "removed" Product\_ID : 124  
Action : "purchased" Product\_ID : 123  
Action : "added" Product\_ID : 123

How can i achieve below sort of output using facet query or any other  
elasticsearch query?

term : 123  
{  
added : 2 times(count num. of times 123 product is added)  
removed : 0 times  
}

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/12d36c7e-cf36-4419-af04-b1b8b2b8aba9%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/12d36c7e-cf36-4419-af04-b1b8b2b8aba9%40googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![Hannes\_Korte](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hannes_korte/32/909_2.png) [@Hannes\_Korte](https://discuss.elastic.co/u/Hannes_Korte)\
**Post date:** [December 13, 2013, 12:40pm UTC](https://discuss.elastic.co/t/how-can-i-achieve-this-result-using-facet/14850/2 "2013-12-13T12:40:29Z")

</div>

Hi,

from your question it is not completely clear what exactly you want to  
do. If you want to have the distinct action counts for a specific  
product, then you can use the simple faceting approach:

"facets": {  
"actions": {  
"terms": {  
"field": "Action",  
"size": 10  
},  
"facet\_filter": {  
"term": {  
"Product\_ID": "123"  
}  
}  
}  
}

This gives a result like this:

"facets": {  
"actions": {  
"\_type": "terms",  
"missing": 0,  
"total": 3,  
"other": 0,  
"terms": [  
{  
"term": "added",  
"count": 2  
},  
{  
"term": "purchased",  
"count": 1  
}  
]  
}  
}

But if you want to get the distinct action counts for all different  
products in your index at once, you can use a new feature called  
aggregations. For this to work you have to use ES 1.0, which is still  
beta. I tested the following search request with ES 1.0.0.beta2:

"aggregations": {  
"productIds": {  
"terms": {  
"field": "Product\_ID"  
},  
"aggregations": {  
"actions": {  
"terms": {  
"field": "Action"  
}  
}  
}  
}  
}

Which produces this:

"aggregations": {  
"productIds": {  
"buckets": [  
{  
"key": 123,  
"doc\_count": 3,  
"actions": {  
"buckets": [  
{  
"key": "added",  
"doc\_count": 2  
},  
{  
"key": "purchased",  
"doc\_count": 1  
}  
]  
}  
},  
{  
"key": 124,  
"doc\_count": 2,  
"actions": {  
"buckets": [  
{  
"key": "removed",  
"doc\_count": 1  
},  
{  
"key": "added",  
"doc\_count": 1  
}  
]  
}  
}  
]  
}  
}

Hope, that helps! Good luck!

Hannes

On 13.12.2013 07:45, abhi patel wrote:

> data is
> 
> Action : "added" Product\_ID : 123  
> Action : "added" Product\_ID : 124  
> Action : "removed" Product\_ID : 124  
> Action : "purchased" Product\_ID : 123  
> Action : "added" Product\_ID : 123
> 
> How can i achieve below sort of output using facet query or any other  
> elasticsearch query?
> 
> term : 123  
> {  
> added : 2 times(count num. of times 123 product is added)  
> removed : 0 times  
> }

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/52AB003D.9050709%40hkorte.com](https://groups.google.com/d/msgid/elasticsearch/52AB003D.9050709%40hkorte.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![abhiage](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@abhiage](https://discuss.elastic.co/u/abhiage)\
**Post date:** [December 17, 2013, 6:29am UTC](https://discuss.elastic.co/t/how-can-i-achieve-this-result-using-facet/14850/3 "2013-12-17T06:29:22Z")

</div>

Thanks you Hannes. It resolved my issue.

On Friday, December 13, 2013 6:10:29 PM UTC+5:30, Hannes Korte wrote:

> Hi,
> 
> from your question it is not completely clear what exactly you want to  
> do. If you want to have the distinct action counts for a specific  
> product, then you can use the simple faceting approach:
> 
> "facets": {  
> "actions": {  
> "terms": {  
> "field": "Action",  
> "size": 10  
> },  
> "facet\_filter": {  
> "term": {  
> "Product\_ID": "123"  
> }  
> }  
> }  
> }
> 
> This gives a result like this:
> 
> "facets": {  
> "actions": {  
> "\_type": "terms",  
> "missing": 0,  
> "total": 3,  
> "other": 0,  
> "terms": [  
> {  
> "term": "added",  
> "count": 2  
> },  
> {  
> "term": "purchased",  
> "count": 1  
> }  
> ]  
> }  
> }
> 
> But if you want to get the distinct action counts for all different  
> products in your index at once, you can use a new feature called  
> aggregations. For this to work you have to use ES 1.0, which is still  
> beta. I tested the following search request with ES 1.0.0.beta2:
> 
> "aggregations": {  
> "productIds": {  
> "terms": {  
> "field": "Product\_ID"  
> },  
> "aggregations": {  
> "actions": {  
> "terms": {  
> "field": "Action"  
> }  
> }  
> }  
> }  
> }
> 
> Which produces this:
> 
> "aggregations": {  
> "productIds": {  
> "buckets": [  
> {  
> "key": 123,  
> "doc\_count": 3,  
> "actions": {  
> "buckets": [  
> {  
> "key": "added",  
> "doc\_count": 2  
> },  
> {  
> "key": "purchased",  
> "doc\_count": 1  
> }  
> ]  
> }  
> },  
> {  
> "key": 124,  
> "doc\_count": 2,  
> "actions": {  
> "buckets": [  
> {  
> "key": "removed",  
> "doc\_count": 1  
> },  
> {  
> "key": "added",  
> "doc\_count": 1  
> }  
> ]  
> }  
> }  
> ]  
> }  
> }
> 
> Hope, that helps! Good luck!
> 
> Hannes
> 
> On 13.12.2013 07:45, abhi patel wrote:
> 
> > data is
> > 
> > Action : "added" Product\_ID : 123  
> > Action : "added" Product\_ID : 124  
> > Action : "removed" Product\_ID : 124  
> > Action : "purchased" Product\_ID : 123  
> > Action : "added" Product\_ID : 123
> > 
> > How can i achieve below sort of output using facet query or any other  
> > elasticsearch query?
> > 
> > term : 123  
> > {  
> > added : 2 times(count num. of times 123 product is added)  
> > removed : 0 times  
> > }

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/f084e8d5-5f26-4af2-9aaf-69a55a1ec768%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/f084e8d5-5f26-4af2-9aaf-69a55a1ec768%40googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<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, 2:00am UTC](https://discuss.elastic.co/t/how-can-i-achieve-this-result-using-facet/14850/4 "2017-07-06T02:00:44Z")

</div>