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)
5)
