# Faceted search counts, the 'select distinct' problem

**URL:** <https://discuss.elastic.co/t/faceted-search-counts-the-select-distinct-problem/6127>\
**Category:** Elasticsearch\
**Created:** [December 12, 2011, 11:17am UTC](https://discuss.elastic.co/t/faceted-search-counts-the-select-distinct-problem/6127 "2011-12-12T11:17:28Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jamie\_Brough](https://avatars.discourse-cdn.com/v4/letter/j/e47c2d/32.png) [@Jamie\_Brough](https://discuss.elastic.co/u/Jamie_Brough)\
**Post date:** [December 12, 2011, 11:17am UTC](https://discuss.elastic.co/t/faceted-search-counts-the-select-distinct-problem/6127/1 "2011-12-12T11:17:28Z")

</div>

We're looking at migrating from ElasticSearch to postgres. The search  
space is very small (10,000 holiday products) and Postgres is  
performant for individual searches. However, we use faceted navigation  
on the frontend, showing the number of venues with available products  
matching the search criteria for alternate dates, regions, categories,  
etc. With postgres, each result needs to be calculated individually;  
ElasticSearch's faceting would be more suitable and has tested well  
with our data.

Our search data has been denormalised to a collection of 'product'  
documents, which basically resembles:

{ venue\_id: 1,  
product\_id: 1,  
venue\_tags: [tag, tag],  
product\_tags: [tag, tag],  
starts\_at: 2011-12-3,  
ends\_at: 2011-12-19,  
loc: [-0.1, 51.5] }

(the actual documents contain detailed venue and product information -  
the venue information is repeated in each product document).

A facet search will give counts for all available products. However,  
we need the number of distinct venues with available products,  
something akin to "SELECT COUNT(DISTINCT venue\_id) FROM ". It sounds like this functionality is planned but not yet  
implemented?

I solution might have been to embed products within a venue document:

{ venue\_id: 1,  
venue\_tags: [tag, tag],  
loc: [-0.1, 51.5],  
products: [  
{ product\_id: 1,  
product\_tags: [tag, tag],  
starts\_at: 2011-12-6,  
ends\_at: 2011-12-19 },  
{ product\_id: 6,  
product\_tags: [tag, tag],  
starts\_at: 2011-12-7,  
ends\_at: 2011-12-21 } ] }

-- but we need to constrain matches to individual products, in order  
to find venues that have 1 or more products available, and the 'cross  
object' search match behaviour described in the nested type  
documentation means this would be not give accurate results. If  
product 1's tags match, an product 2's dates match - that should not  
be a match as no single product is a match.

Does anyone have any suggestions how to better approach this, search  
products but somehow aggregate the counts for faceted search by the  
venue?

Jamie

---

<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:45am UTC](https://discuss.elastic.co/t/faceted-search-counts-the-select-distinct-problem/6127/2 "2017-07-06T03:45:44Z")

</div>