Sorting all hits by a nested field value

Hi!

Is it possible to sort a query result by the value of a nested field, across all hits?

To give you an example, let's say I am storing sellers and their products. A document stores the information of a single seller, including a list of their products as nested documents. So the mapping would be something like this:

{
  "mapping": {
    "properties": {
      "name": {
        "type": "text"
      },
      "products": {
        "type": "nested",
        "properties": {
          "name": {
            "type": "text"
          },
          "price": {
            "type": "integer"
          }
        }
      }
    }
  }
}

My question is, how do I retrieve all products and their vendors, listed from most expensive to cheapest? If I were working with a relational database, products and sellers would be stored in separate tables and my SQL query would look like this:

SELECT products.name, products.price, sellers.name
FROM products INNER JOIN sellers ON products.seller_id=sellers.id
ORDER BY products.price DESC;

Is it possible to achieve the same thing in Elasticsearch, with the mapping shown above? I am aware of nested queries, but so far I have only been able to sort the inner hits within each document. So I can get a result like this:

Seller A
	- Product 3, $100
	- Product 1, $80
	- Product 2, $10
Seller B
	- Product 5, $90
	- Product 4, $5

But what I need is this:

Product 3, Seller A, $100
Product 5, Seller B, $90
Product 1, Seller A, $80
Product 2, Seller A, $10
Product 4, Seller B, $5

Is this possible? Any help is greatly appreciated.

Why not store it as 5 separate documents (as in the results you are looking for) instead of using a single nested one?

Thanks for the suggestion! In the simple example I have given, this would indeed be feasible. In my
actual use-case however, "flattening" the mapping would introduce a huge redundancy. I have hundreds of fields, several of them are nested or vector-valued. (Let's say the sellers not only have names, but also addresses, phone numbers, etc... repeating all of these for every product would really blow up the size of the index.)

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