

Table of Contents
1. Overview
Solr provide facility to group or cluster search result into categories that let users drill into search results by any value in any field. Solr faceting is used in many applications to give an overall idea about how the data resides in the index.
In our Previous article, we have discuss about how solr provide more details about our index using Solr pivot faceting or decision tree faceting or sub faceting.we can calculate sub facet of parent facet or generate tree like structure of data and display it in the application which helps us take better decision.
In this article, we will discuss three advance feature of solr pivot faceting.
2. Stats Component with pivot facet Example
Solr stats component provide statistics on any fields.We can also compute statistics on pivot facet.
stats local parameter is used with facet.pivot field by specifying it’s tag.
2.1 Syntax
stats=true stats.field={!tag=<TAG_NAME> min=true|false max=true|false}FIELD_NAME facet=true facet.pivot={!stats=<TAG_NAME>}FIELD_NAME_1,FIELD_NAME_2
2.2 Query
Here we are using our techproduct data.We have generated all required statistic on price field and use that tag in facet.pivot.
http://localhost:8983/solr/AdvancePivotFaceting/select? indent=on& q=*:*& wt=json& rows=0& stats=true& stats.field={!tag=piv1 min=true max=true mean=true sum=true stddev=true sumOfSquares=true}price& &facet=true& facet.pivot={!stats=piv1}cat,popularity& facet.pivot.mincount=3
2.3 Output
{ "responseHeader":{ "status":0, "QTime":9, "params":{ "":"", "q":"*:*", "indent":"on", "stats":"true", "facet.pivot":"{!stats=piv1}cat,popularity", "rows":"0", "wt":"json", "facet":"true", "facet.pivot.mincount":"3", "stats.field":"{!tag=piv1 min=true max=true mean=true sum=true stddev=true sumOfSquares=true}price"}}, "response":{"numFound":32,"start":0,"docs":[] }, "facet_counts":{ "facet_queries":{}, "facet_fields":{}, "facet_ranges":{}, "facet_intervals":{}, "facet_heatmaps":{}, "facet_pivot":{ "cat,popularity":[{ "field":"cat", "value":"electronics", "count":12, "pivot":[{ "field":"popularity", "value":7, "count":4, "stats":{ "stats_fields":{ "price":{ "min":74.98999786376953, "max":649.989990234375, "sum":1534.880012512207, "sumOfSquares":767329.5119574405, "mean":383.72000312805176, "stddev":243.83419828172336}}}}, { "field":"popularity", "value":6, "count":3, "stats":{ "stats_fields":{ "price":{ "min":92.0, "max":350.0, "sum":621.9900054931641, "sumOfSquares":163360.40207742923, "mean":207.3300018310547, "stddev":131.1548952567799}}}}], "stats":{ "stats_fields":{ "price":{ "min":11.5, "max":649.989990234375, "sum":2772.3200187683105, "sumOfSquares":1124646.1665653111, "mean":252.02909261530095, "stddev":206.38334908511874}}}}, { "field":"cat", "value":"currency", "count":4, "stats":{ "stats_fields":{ "price":{ "min":null, "max":null, "sum":0.0, "sumOfSquares":0.0, "mean":"NaN", "stddev":0.0}}}}, { "field":"cat", "value":"memory", "count":3, "stats":{ "stats_fields":{ "price":{ "min":74.98999786376953, "max":185.0, "sum":259.98999786376953, "sumOfSquares":39848.49977960816, "mean":129.99499893188477, "stddev":77.78881850887514}}}}]}}, "stats":{ "stats_fields":{ "price":{ "min":0.0, "max":2199.0, "sum":5251.270030975342, "sumOfSquares":6038619.175900028, "mean":328.20437693595886, "stddev":536.3536996709846}}}}
3. Facet Queries with pivot facet Example
Solr provide facility to combine facet queries with pivot facet same as stats component.Query local parameter is used to refer facet.query instances.
“Note that facet.query instances will be computed for each pivot facet fields”
2.1 Syntax
facet=true facet.query={!tag=<TAG_NAME>}<FIELD_NAME>:<VALUE> facet.pivot={!query=<TAG_NAME>}FIELD_NAME_1,FIELD_NAME_2
2.2 Query
In below query we have execute facet.query over name field and pass query local parameter in facet.pivot.
http://localhost:8983/solr/AdvancePivotFaceting/select? q=*:*& indent=on& wt=json& rows=0& facet=true& facet.query={!tag=q1}name:*a*& facet.pivot={!query=q1}cat,inStock& facet.pivot.mincount=3
2.3 Output
{ "responseHeader":{ "status":0, "QTime":33, "params":{ "facet.query":"{!tag=q1}name:*a*", "q":"*:*", "indent":"on", "facet.pivot":"{!query=q1}cat,inStock", "rows":"0", "wt":"json", "facet":"true", "facet.pivot.mincount":"3"}}, "response":{"numFound":32,"start":0,"docs":[] }, "facet_counts":{ "facet_queries":{ "{!tag=q1}name:*a*":17}, "facet_fields":{}, "facet_ranges":{}, "facet_intervals":{}, "facet_heatmaps":{}, "facet_pivot":{ "cat,inStock":[{ "field":"cat", "value":"electronics", "count":12, "queries":{ "{!tag=q1}name:*a*":11}, "pivot":[{ "field":"inStock", "value":true, "count":8, "queries":{ "{!tag=q1}name:*a*":8}}, { "field":"inStock", "value":false, "count":4, "queries":{ "{!tag=q1}name:*a*":3}}]}, { "field":"cat", "value":"currency", "count":4, "queries":{ "{!tag=q1}name:*a*":1}, "pivot":[{ "field":"inStock", "value":true, "count":4, "queries":{ "{!tag=q1}name:*a*":1}}]}, { "field":"cat", "value":"memory", "count":3, "queries":{ "{!tag=q1}name:*a*":3}, "pivot":[{ "field":"inStock", "value":true, "count":3, "queries":{ "{!tag=q1}name:*a*":3}}]}]}}}
4. Range Facet with pivot facet Example
We can also combine range facet queries with pivot faceting same as a normal facet.query.
query local parameter is used for combining range facet with pivot facet.it will be computed for all pivot facet fields.
“we can use the same tag in more than one facet.query params”
4.1 Syntax
facet=true facet.query={!tag=<TAG_NAME>}<FIELD_NAME>:[lbound TO ubound] facet.pivot={!query=<TAG_NAME>}FIELD_NAME_1,FIELD_NAME_2
4.2 Query
In below example we have use three fields, two numeric fields and one date type fields to generate range facet.
http://localhost:8983/solr/AdvancePivotFaceting/select? q=*:*& indent=on& wt=json& rows=0& facet=true& facet.query={!tag=q1}price:[* TO *]& facet.query={!tag=q1}popularity:[1 TO 10]& facet.query={!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]& facet.pivot={!query=q1}cat,inStock& facet.pivot.mincount=3
4.3 Output
{ "responseHeader":{ "status":0, "QTime":22, "params":{ "facet.query":["{!tag=q1}price:[* TO *]", "{!tag=q1}popularity:[1 TO 10]", "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]"], "q":"*:*", "indent":"on", "facet.pivot":"{!query=q1}cat,inStock", "rows":"0", "wt":"json", "facet":"true", "facet.pivot.mincount":"3"}}, "response":{"numFound":32,"start":0,"docs":[] }, "facet_counts":{ "facet_queries":{ "{!tag=q1}price:[* TO *]":16, "{!tag=q1}popularity:[1 TO 10]":14, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":11}, "facet_fields":{}, "facet_ranges":{}, "facet_intervals":{}, "facet_heatmaps":{}, "facet_pivot":{ "cat,inStock":[{ "field":"cat", "value":"electronics", "count":12, "queries":{ "{!tag=q1}price:[* TO *]":11, "{!tag=q1}popularity:[1 TO 10]":11, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":11}, "pivot":[{ "field":"inStock", "value":true, "count":8, "queries":{ "{!tag=q1}price:[* TO *]":7, "{!tag=q1}popularity:[1 TO 10]":7, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":7}}, { "field":"inStock", "value":false, "count":4, "queries":{ "{!tag=q1}price:[* TO *]":4, "{!tag=q1}popularity:[1 TO 10]":4, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":4}}]}, { "field":"cat", "value":"currency", "count":4, "queries":{ "{!tag=q1}price:[* TO *]":0, "{!tag=q1}popularity:[1 TO 10]":0, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":0}, "pivot":[{ "field":"inStock", "value":true, "count":4, "queries":{ "{!tag=q1}price:[* TO *]":0, "{!tag=q1}popularity:[1 TO 10]":0, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":0}}]}, { "field":"cat", "value":"memory", "count":3, "queries":{ "{!tag=q1}price:[* TO *]":2, "{!tag=q1}popularity:[1 TO 10]":2, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":3}, "pivot":[{ "field":"inStock", "value":true, "count":3, "queries":{ "{!tag=q1}price:[* TO *]":2, "{!tag=q1}popularity:[1 TO 10]":2, "{!tag=q1}manufacturedate_dt:[1999-01-01T00:00:00Z TO 2018-01-01T00:00:00Z]":3}}]}]}}}
5. Conclusion
In this article, we have discussed how to combine stats component, facet queries and facet range query with example.
6. References
Refer Solr Reference Guide, Solr pivot faceting, Solr range faceting, solr field value faceting for more details.