# Pivot facets **URL:** \ **Category:** Elasticsearch\ **Created:** [May 24, 2011, 8:34pm UTC](https://discuss.elastic.co/t/pivot-facets/4470 "2011-05-24T20:34:48Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Greg](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Greg](https://discuss.elastic.co/u/Greg)\ **Post date:** [May 24, 2011, 8:34pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/1 "2011-05-24T20:34:48Z")
Hi all Does ES currently support, or plan to support pivot faceting like Solr? I am trying to create a hierarchical facet structure, for example: State - California (23) - Los Angeles (9) - San Francisco (14) - Florida (5) - Miami (5) Many thanks Greg ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [May 24, 2011, 9:28pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/2 "2011-05-24T21:28:55Z")
Yes, there is plan to support it, but note that the complexity of the solution (while still maintaining good search performance). Also note that hierarchical and pivot talbe are two completely different things. Note, that some facet do allow for "two" level aggregation, such as terms stats, and histograms (can have custom value field). The simplest is an hierarchical terms facet. But, of course, what happens when you want histo -\> terms - \> histo? And last, there is a the pivot table, which is very different from hierarchical facets. Many people confuse between teh two. On Tuesday, May 24, 2011 at 11:34 PM, Greg wrote: Hi all > Does ES currently support, or plan to support pivot faceting like > Solr? I am trying to create a hierarchical facet structure, for > example: > > State > > - California (23) > > - Los Angeles (9) > - San Francisco (14) > > - Florida (5) > > - Miami (5) > > Many thanks > Greg ---
**Author:** ![Greg](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Greg](https://discuss.elastic.co/u/Greg)\ **Post date:** [May 24, 2011, 10:55pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/3 "2011-05-24T22:55:58Z")
Hi Shay Could you briefly clarify the difference between a hierarchical facet and pivot facet? I had been reading the following article, which led me to believe they are the same thing: [http://solr.pl/en/2010/10/25/hierarchical-faceting-pivot-facets-in-trunk/](http://solr.pl/en/2010/10/25/hierarchical-faceting-pivot-facets-in-trunk/) Also, you mentioned that it is possible to do two-level aggregation of terms facets. I have checked the docs, but cannot find how to do this. What is the syntax for this? Many thanks Greg ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [May 24, 2011, 11:24pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/4 "2011-05-24T23:24:46Z")
On Wednesday, May 25, 2011 at 1:55 AM, Greg wrote: Hi Shay > Could you briefly clarify the difference between a hierarchical facet > and pivot facet? I had been reading the following article, which led > me to believe they are the same thing: > [Hierarchical faceting – Pivot facets in trunk – Solr.pl](http://solr.pl/en/2010/10/25/hierarchical-faceting-pivot-facets-in-trunk/) > [Pivot table - Wikipedia](http://en.wikipedia.org/wiki/Pivot_table), thats completely different from hierarchy one, or even what some call pivot. > > Also, you mentioned that it is possible to do two-level aggregation of > terms facets. I have checked the docs, but cannot find how to do this. > What is the syntax for this? > Terms Stats facet, but thats only support where the value is numeric ( [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html)). > > Many thanks > Greg ---
**Author:** ![plaflamme](https://avatars.discourse-cdn.com/v4/letter/p/a88e57/32.png) [@plaflamme](https://discuss.elastic.co/u/plaflamme)\ **Post date:** [January 17, 2012, 8:20pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/5 "2012-01-17T20:20:58Z")
> Terms Stats facet, but thats only support where the value is numeric ( > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html) > ). I was wondering whether the term\_stats facet could support string value fields? It currently doesn't (as stated above), so I'm wondering if this is because of the distributed nature of ES or simply a TODO that hasn't been TODOed. Thanks, Philippe ---
**Author:** ![Mark\_Huang](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Mark\_Huang](https://discuss.elastic.co/u/Mark_Huang)\ **Post date:** [January 17, 2012, 9:09pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/6 "2012-01-17T21:09:47Z")
What kinds of statistical facets could you return besides count (which you can get with a simple terms facet)? The other statistical facets (total, average, min, max, etc.) don't really make sense for string values. Do your string values contain numbers within them (as substrings)? You could write a script that pulls them out and converts the substrings to integers. I am working on a native Java script that can apply regex patterns to search results, and can be used in facet calculations. Is this something that would be interesting to anyone else? Is there an Issue for this? --Mark On Jan 17, 2012, at 12:20 PM, Philippe Laflamme wrote: > Terms Stats facet, but thats only support where the value is numeric ( [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html)). > > I was wondering whether the term\_stats facet could support string value fields? It currently doesn't (as stated above), so I'm wondering if this is because of the distributed nature of ES or simply a TODO that hasn't been TODOed. > > Thanks, > Philippe ---
**Author:** ![plaflamme](https://avatars.discourse-cdn.com/v4/letter/p/a88e57/32.png) [@plaflamme](https://discuss.elastic.co/u/plaflamme)\ **Post date:** [January 17, 2012, 9:55pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/7 "2012-01-17T21:55:25Z")
When the "value\_field" is a string, it would return frequencies for each distinct value. So you'd obtain the frequency of the terms for each of the values of "key\_field". key\_field\_a : value\_field\_a : freq value\_field\_b: freq key\_field\_b: value\_field\_a : freq value\_field\_b: freq It's a cross-tabulation : [Contingency table - Wikipedia](http://en.wikipedia.org/wiki/Cross_tabulation) It's been referred to as "hierarchical facet" and "pivot facet". Philippe On Tue, Jan 17, 2012 at 16:09, Mark Huang [mark.l.huang@gmail.com](mailto:mark.l.huang@gmail.com) wrote: > What kinds of statistical facets could you return besides count (which you > can get with a simple terms facet)? The other statistical facets (total, > average, min, max, etc.) don't really make sense for string values. Do your > string values contain numbers within them (as substrings)? You could write > a script that pulls them out and converts the substrings to integers. > > I am working on a native Java script that can apply regex patterns to > search results, and can be used in facet calculations. Is this something > that would be interesting to anyone else? Is there an Issue for this? > > --Mark > > On Jan 17, 2012, at 12:20 PM, Philippe Laflamme wrote: > > > Terms Stats facet, but thats only support where the value is numeric ( > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html) > > ). > > I was wondering whether the term\_stats facet could support string value > fields? It currently doesn't (as stated above), so I'm wondering if this is > because of the distributed nature of ES or simply a TODO that hasn't been > TODOed. > > Thanks, > Philippe ---
**Author:** ![Mark\_Huang](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Mark\_Huang](https://discuss.elastic.co/u/Mark_Huang)\ **Post date:** [January 17, 2012, 10:38pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/8 "2012-01-17T22:38:36Z")
You can derive frequency from terms facet count. If the value string of interest is the entire field, just specify the field name (must be not\_analyzed) to facet on. If it is an analyzed field, or you need to pull out a substring, you need to use script\_field or script to perform the necessary textual transformation, which can be very slow, but it will basically work: > **[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. As of now, you can only get top N terms and their counts with a terms facet; unfortunately "all terms" doesn't exist yet: > > > Currently terms facet has an option all\_terms which is expected to bring all the… terms in the index (for a > field) with frequency counts on them. However, it does not work. Can you fix this? > > Discussed in > http://elasticsearch-users.115913.n3.nabble.com/Terms-facet-all-terms-does-not-work-td3568708.html [http://elasticsearch-users.115913.n3.nabble.com/Terms-facet-all-terms-does-not-work-td3568708.html](http://elasticsearch-users.115913.n3.nabble.com/Terms-facet-all-terms-does-not-work-td3568708.html) --Mark On Jan 17, 2012, at 1:55 PM, Philippe Laflamme wrote: > When the "value\_field" is a string, it would return frequencies for each distinct value. So you'd obtain the frequency of the terms for each of the values of "key\_field". > > key\_field\_a : > value\_field\_a : freq > value\_field\_b: freq > key\_field\_b: > value\_field\_a : freq > value\_field\_b: freq > > It's a cross-tabulation : [Contingency table - Wikipedia](http://en.wikipedia.org/wiki/Cross_tabulation) > > It's been referred to as "hierarchical facet" and "pivot facet". > > Philippe > > On Tue, Jan 17, 2012 at 16:09, Mark Huang [mark.l.huang@gmail.com](mailto:mark.l.huang@gmail.com) wrote: > What kinds of statistical facets could you return besides count (which you can get with a simple terms facet)? The other statistical facets (total, average, min, max, etc.) don't really make sense for string values. Do your string values contain numbers within them (as substrings)? You could write a script that pulls them out and converts the substrings to integers. > > I am working on a native Java script that can apply regex patterns to search results, and can be used in facet calculations. Is this something that would be interesting to anyone else? Is there an Issue for this? > > --Mark > > On Jan 17, 2012, at 12:20 PM, Philippe Laflamme wrote: > > > Terms Stats facet, but thats only support where the value is numeric ( [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html)). > > > > I was wondering whether the term\_stats facet could support string value fields? It currently doesn't (as stated above), so I'm wondering if this is because of the distributed nature of ES or simply a TODO that hasn't been TODOed. > > > > Thanks, > > Philippe ---
**Author:** ![plaflamme](https://avatars.discourse-cdn.com/v4/letter/p/a88e57/32.png) [@plaflamme](https://discuss.elastic.co/u/plaflamme)\ **Post date:** [January 18, 2012, 1:00am UTC](https://discuss.elastic.co/t/pivot-facets/4470/9 "2012-01-18T01:00:34Z")
I'm not sure I understand what you're saying. How do you suggest making a cross-tabulation using terms\_facet. I don't think you can derive the frequencies of a cross-tabulation without making N queries (where N is the number of columns, or rows in your table). Or maybe a single query with N filtered terms\_facet (which probably results in N queries anyway). Are you suggesting otherwise? Thanks Philippe On Tue, Jan 17, 2012 at 17:38, Mark Huang [mark.l.huang@gmail.com](mailto:mark.l.huang@gmail.com) wrote: > You can derive frequency from terms facet count. If the value string of > interest is the entire field, just specify the field name (must be > not\_analyzed) to facet on. If it is an analyzed field, or you need to pull > out a substring, you need to use script\_field or script to perform the > necessary textual transformation, which can be very slow, but it will > basically work: > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-facet.html) > > As of now, you can only get top N terms and their counts with a terms > facet; unfortunately "all terms" doesn't exist yet: > > [all\_terms capability of terms facet · Issue #1530 · elastic/elasticsearch · GitHub](https://github.com/elasticsearch/elasticsearch/issues/1530) > > [http://elasticsearch-users.115913.n3.nabble.com/Terms-facet-all-terms-does-not-work-td3568708.html](http://elasticsearch-users.115913.n3.nabble.com/Terms-facet-all-terms-does-not-work-td3568708.html) > > --Mark > > On Jan 17, 2012, at 1:55 PM, Philippe Laflamme wrote: > > When the "value\_field" is a string, it would return frequencies for each > distinct value. So you'd obtain the frequency of the terms for each of the > values of "key\_field". > > key\_field\_a : > value\_field\_a : freq > value\_field\_b: freq > key\_field\_b: > value\_field\_a : freq > value\_field\_b: freq > > It's a cross-tabulation : [Contingency table - Wikipedia](http://en.wikipedia.org/wiki/Cross_tabulation) > > It's been referred to as "hierarchical facet" and "pivot facet". > > Philippe > > On Tue, Jan 17, 2012 at 16:09, Mark Huang [mark.l.huang@gmail.com](mailto:mark.l.huang@gmail.com) wrote: > > > What kinds of statistical facets could you return besides count (which > > you can get with a simple terms facet)? The other statistical facets > > (total, average, min, max, etc.) don't really make sense for string values. > > Do your string values contain numbers within them (as substrings)? You > > could write a script that pulls them out and converts the substrings to > > integers. > > > > I am working on a native Java script that can apply regex patterns to > > search results, and can be used in facet calculations. Is this something > > that would be interesting to anyone else? Is there an Issue for this? > > > > --Mark > > > > On Jan 17, 2012, at 12:20 PM, Philippe Laflamme wrote: > > > > > Terms Stats facet, but thats only support where the value is numeric ( > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/terms-stats-facet.html) > > > ). > > > > I was wondering whether the term\_stats facet could support string value > > fields? It currently doesn't (as stated above), so I'm wondering if this is > > because of the distributed nature of ES or simply a TODO that hasn't been > > TODOed. > > > > Thanks, > > Philippe ---
**Author:** ![Mark\_Huang](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Mark\_Huang](https://discuss.elastic.co/u/Mark_Huang)\ **Post date:** [January 18, 2012, 8:11am UTC](https://discuss.elastic.co/t/pivot-facets/4470/10 "2012-01-18T08:11:54Z")
Sorry, I'm just reading the thread from last year now. If it existed, I think a hierarchical terms facet is what you are looking for. The only two-level aggregate functions available now are terms stats and histogram, and the second level aggregate function for both only calculates numerical statistics, not term frequencies. --Mark On Jan 17, 2012, at 5:00 PM, Philippe Laflamme wrote: > I'm not sure I understand what you're saying. How do you suggest making a cross-tabulation using terms\_facet. > > I don't think you can derive the frequencies of a cross-tabulation without making N queries (where N is the number of columns, or rows in your table). Or maybe a single query with N filtered terms\_facet (which probably results in N queries anyway). > > Are you suggesting otherwise? > > Thanks > Philippe ---
**Author:** ![plaflamme](https://avatars.discourse-cdn.com/v4/letter/p/a88e57/32.png) [@plaflamme](https://discuss.elastic.co/u/plaflamme)\ **Post date:** [January 18, 2012, 1:42pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/11 "2012-01-18T13:42:30Z")
Yes indeed. And I'm wondering why the term\_stats facet only supports numerical fields as the value\_field. Is this simply because it hasn't been done or it's because of the nature of ES which makes it impossible (distributed distinct count). Philippe On Wed, Jan 18, 2012 at 03:11, Mark Huang [mark.l.huang@gmail.com](mailto:mark.l.huang@gmail.com) wrote: > Sorry, I'm just reading the thread from last year now. If it existed, I > think a hierarchical terms facet is what you are looking for. The only > two-level aggregate functions available now are terms stats and histogram, > and the second level aggregate function for both only calculates numerical > statistics, not term frequencies. > > --Mark > > On Jan 17, 2012, at 5:00 PM, Philippe Laflamme wrote: > > > I'm not sure I understand what you're saying. How do you suggest making > > a cross-tabulation using terms\_facet. > > > > I don't think you can derive the frequencies of a cross-tabulation > > without making N queries (where N is the number of columns, or rows in your > > table). Or maybe a single query with N filtered terms\_facet (which probably > > results in N queries anyway). > > > > Are you suggesting otherwise? > > > > Thanks > > Philippe ---
**Author:** ![Mark\_Huang](https://avatars.discourse-cdn.com/v4/letter/m/b77776/32.png) [@Mark\_Huang](https://discuss.elastic.co/u/Mark_Huang)\ **Post date:** [January 18, 2012, 7:12pm UTC](https://discuss.elastic.co/t/pivot-facets/4470/12 "2012-01-18T19:12:44Z")
Shay can correct me if I'm wrong, but I doubt it's because of the nature of ES. As you said, there's no fundamental reason it couldn't be supported; ES could make the same queries you could to create your attribute hierarchy; but the practical issues, such as how to optimize the queries, and how to limit their memory consumption (both in a generalized fashion), are quite difficult to think about. If you are trying to create a pivot table in the MS Excel sense of the word, however, I'm not sure you need hierarchical facets, you just need the documents with your AxB different attributes, and can create the table yourself in your application. In the Wikipedia example, you could synthesize a script field called Gender\_Handedness that is defined as "doc.gender + '\_' + doc.handedness", then get counts for each combination of "Male\_Left-handed", "Female\_Left-handed", etc., and create the pivot table in your app. --Mark On Jan 18, 2012, at 5:42 AM, Philippe Laflamme wrote: > Yes indeed. And I'm wondering why the term\_stats facet only supports numerical fields as the value\_field. Is this simply because it hasn't been done or it's because of the nature of ES which makes it impossible (distributed distinct count). > > Philippe ---
**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:42am UTC](https://discuss.elastic.co/t/pivot-facets/4470/13 "2017-07-06T03:42:19Z")