# Unique values query without aggregations

**URL:** <https://discuss.elastic.co/t/unique-values-query-without-aggregations/168442>\
**Category:** Elasticsearch\
**Created:** [February 14, 2019, 3:46pm UTC](https://discuss.elastic.co/t/unique-values-query-without-aggregations/168442 "2019-02-14T15:46:51Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Itay\_Bittan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/itay_bittan/32/23527_2.png) [@Itay\_Bittan](https://discuss.elastic.co/u/Itay_Bittan)\
**Post date:** [February 14, 2019, 3:46pm UTC](https://discuss.elastic.co/t/unique-values-query-without-aggregations/168442/1 "2019-02-14T15:46:51Z")

</div>

Hey,

We have an index of unique products where each document represents a single product, with the following fields: product\_id, group\_id, group\_score, and product\_score.  
Consider the following index:  
{  
"product\_id": "100-001",  
"group\_id": "100",  
"group\_score": 100,  
"product\_score": 60,  
},  
{  
"product\_id": "100-002",  
"group\_id": "100",  
"group\_score": 100,  
"product\_score": 40,  
},  
{  
"product\_id": "100-001",  
"group\_id": "100",  
"group\_score": 100,  
"product\_score": 50,  
},  
{  
"product\_id": "200-001",  
"group\_id": "200",  
"group\_score": 73,  
"product\_score": 20,  
},  
{  
"product\_id": "200-002",  
"group\_id": "200",  
"group\_score": 73,  
"product\_score": 53,  
}

Every group contains ~1-200 products.  
We are trying to a query that matches the following conditions:

1. Products should be sorted by their group\_score (desc).
2. No more than one product per group\_id.
3. Get the product with the highest product\_score within the group.

For example, applying the query on the above should return:  
{  
"product\_id": "100-001"  
},  
{  
"product\_id": "200-002"  
}

We ended up with the following query:  
{  
"size": 0,  
"aggs": {  
"group\_by\_group\_id": {  
"terms": {  
"field": "group\_id",  
"order":{  
"max\_group\_score":"desc"  
}  
},  
"aggs": {  
"top\_scores\_hits": {  
"top\_hits": {  
"sort": [  
{  
"product\_score": {  
"order": "desc"  
}  
}  
],  
"size": 1  
}  
},  
"max\_group\_score":{  
"max":{  
"field":"group\_score"  
}  
}  
}  
}  
}  
}

The problem is that the query is really slow because of the aggregations and the search performance is important.

We would love to hear your opinion about a better/efficient solution.  
Changing the index structure is tolerable.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [February 14, 2019, 4:07pm UTC](https://discuss.elastic.co/t/unique-values-query-without-aggregations/168442/2 "2019-02-14T16:07:59Z")

</div>

I imagine the slow bit might be the top-hits. Try removing that to check.

There will be redundancy in the top-hits retrieval because each shard will be returning more than the 10 groups (the `shard_size` setting controls this value) but on the final reduction many of these retrieved hits will be discarded because they are not competitive in the overall results.  
If that's true maybe making 2 requests would be faster (one to find the top groups then another to fetch top products for the selected groups.)

---

<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:** [March 14, 2019, 4:08pm UTC](https://discuss.elastic.co/t/unique-values-query-without-aggregations/168442/3 "2019-03-14T16:08:05Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.