CODEWITHSUNDEEP
sortingelasticsearchnestedaggregation
Daniel M
Daniel M

Reputation: 3379

Elasticsearch group and order by nested field's min value

I've got a structure of products which are available in different stores with different prices:

[{
  "name": "SomeProduct",
  "store_prices": [
    {
      "store": "FooStore1",
      "price": 123.45
    },
    {
      "store": "FooStore2",
      "price": 345.67
    }
  ]
},{
  "name": "OtherProduct",
  "store_prices": [
    {
      "store": "FooStore1",
      "price": 456.78
    },
    {
      "store": "FooStore2",
      "price": 234.56
    }
  ]
}]

I want to show a list of products, ordered by the lowest price ascending, limited to 10 results, in this way:

How to do this? I've tried the nested aggregation approach described in https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html but it only returns the min price of all products, not the respective min price for each product:

{
  "_source": [
    "name",
    "store_prices.price"
  ],
  "query": {
    "match_all": {}
  },
  "sort": {
    "store_prices.price": "asc"
  },
  "aggs": {
    "stores": {
      "nested": {
        "path": "store_prices"
      },
      "aggs": {
        "min_price": {"min": {"field": "store_prices.price"}}
      }
    }
  },
  "from": 0,
  "size": 10
}

In SQL, what I want to do could be described using the following query. I'm afraid I'm thinking too much "in sql":

SELECT
  p.name,
  MIN(s.price) AS price
FROM
  products p
INNER JOIN
  store_prices s ON s.product_id = p.id
GROUP BY
  p.id
ORDER BY
  price ASC
LIMIT 10

Upvotes: 1

Views: 1263

Answers (1)

davide
davide

Reputation: 1948

You need a nested sorting:

{
   "query": // HERE YOUR QUERY,
   "sort": {
     "store_prices.price": {
       "order" : "asc",
       "nested_path" : "store_prices",
       "nested_filter": {
          // HERE THE FILTERS WHICH ARE EVENTUALLY 
          // FILTERING OUT SOME OF YOUR STORES
       }
     }
   }
}

Pay attention that you have to repeat the eventual nested queries inside the nested filter field. You find then the price in the score field of the response.

Upvotes: 2

Related Questions