Trouble writing a query with a basic search, a term aggregation, top_hits and sorting both on top_hits and buckets

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:


2)

3)

4)
image
5)
image

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

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