# Trouble writing a query with a basic search, a term aggregation, top\_hits and sorting both on top\_hits and buckets

**URL:** <https://discuss.elastic.co/t/trouble-writing-a-query-with-a-basic-search-a-term-aggregation-top-hits-and-sorting-both-on-top-hits-and-buckets/114925>\
**Category:** Elasticsearch\
**Created:** [January 10, 2018, 5:46pm UTC](https://discuss.elastic.co/t/trouble-writing-a-query-with-a-basic-search-a-term-aggregation-top-hits-and-sorting-both-on-top-hits-and-buckets/114925 "2018-01-10T17:46:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ikaer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ikaer/32/25077_2.png) [@Ikaer](https://discuss.elastic.co/u/Ikaer)\
**Post date:** [January 10, 2018, 5:46pm UTC](https://discuss.elastic.co/t/trouble-writing-a-query-with-a-basic-search-a-term-aggregation-top-hits-and-sorting-both-on-top-hits-and-buckets/114925/1 "2018-01-10T17:46:19Z")

</div>

Hi,

I have trouble to write a specific query in elasticsearch.

The context:  
I have an index where each document represents a “SKU”: a declination of a product (symbolized by pId).  
For example, the first 3 documents are declinations in color and price of product 235.  
BS is for “Best SKU”: for a given product, SKUs are sorted from the most representative to the less representative.  
After a search, only best SKUs matching the search should be used for further sorting or aggregations.

this is a script to create a test index:

```
POST /test/skus/DOC_1
{
  "pId":235,
  "BS":3,
  "color":"red",
  "price":59.00
}
POST /test/skus/DOC_2
{
  "pId":235,
  "BS":2,
  "color":"red",
  "price":29.00
}
POST /test/skus/DOC_3
{
  "pId":235,
  "BS":1,
  "color":"green",
  "price":69.00
}
POST /test/skus/DOC_4
{
  "pId":236,
  "BS":2,
  "color":"blue",
  "price":19.00
}
POST /test/skus/DOC_5
{
  "pId":236,
  "BS":1,
  "color":"red",
  "price":99.00
}
POST /test/skus/DOC_6
{
  "pId":236,
  "BS":3,
  "color":"red",
  "price":39.00
}
POST /test/skus/DOC_7
{
  "pId":237,
  "BS":2,
  "color":"red",
  "price":10.00
}
POST /test/skus/DOC_8
{
  "pId":237,
  "BS":1,
  "color":"blue",
  "price":50.00
}
POST /test/skus/DOC_9
{
  "pId":237,
  "BS":3,
  "color":"green",
  "price":20.00
}

```

The query I'm trying to write is a query that search, for example, the red SKUs, do an aggregation by product (using term aggregation and pId), only retains the best SKU in each bucket and THEN sort those buckets on the price of best SKU.

Here is what I've got so far:

```
GET /test/skus/_search
{
  "size": 0, 
  "query": {
    "term": {
      "color": {
        "value": "red"
      }
    }
  },
  "aggs": {
    "bypId": {
      "terms": {
        "field": "pId",
        "size": 10
      },
      "aggs": {
        "mytophits": {
          "top_hits": {
            "size": 1,
            "sort": ["BS"]
          }
        }
      }
    }
  }
}

```

I don't know from here how to sort on buckets price.

I've done some screenshot to better explain what I'm trying to achieve:

1. 

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/6/f643a39591f200d1981c39e6312389c53dc3f50f.png)  
2)  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/3/e36a95509f3b4b5e5a5cefadb969932541306e52.png)  
3)  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/5/95248c97c848f81717a533efea085d58bfb5fd1e.png)  
4)  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/d/cd81da8604a31ff11cc50f69c8ec00508fd6a0bf.png)  
5)  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/7/e79fd4324f6c9f1fa45ec5fe9e491bcde85d5a3f.png)

---

<div class="post-metadata">

**Author:** ![Ikaer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ikaer/32/25077_2.png) [@Ikaer](https://discuss.elastic.co/u/Ikaer)\
**Post date:** [January 29, 2018, 3:18am UTC](https://discuss.elastic.co/t/trouble-writing-a-query-with-a-basic-search-a-term-aggregation-top-hits-and-sorting-both-on-top-hits-and-buckets/114925/2 "2018-01-29T03:18:44Z")

</div>

Bump. Anyone ?  
An answer that tells me that it is not possible to do such a thing is also welcomed 🙂

---

<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:** [February 26, 2018, 3:18am UTC](https://discuss.elastic.co/t/trouble-writing-a-query-with-a-basic-search-a-term-aggregation-top-hits-and-sorting-both-on-top-hits-and-buckets/114925/3 "2018-02-26T03:18:58Z")

</div>

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