Elasticsearch group and order by nested field's min value

Question

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:

SomeProduct: 123.45 USD
OtherProduct: 234.56 USD

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

@Val: Updated in the question; basically, the example query from the docs. — Daniel M
– Daniel M, Commented Sep 4, 2017 at 15:29

davide · Accepted Answer · 2017-09-04 19:02:22Z

2

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.

answered Sep 4, 2017 at 19:02

davide

1,9482 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Elasticsearch group and order by nested field's min value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related