How to sort by aggregate from another index/type

Question

i have a webshop and need to sort by the aggregate sales data. Example documents for products:

{
    "id": 123,
    "title": "foo product",
    "picture": "https://i.picsum.photos/id/959/200/300.jpghttps://i.picsum.photos/id/959/200/300.jpg"
}

Example documents for sales:

{
    "product_id": 123,
    "date": "2020-01-01",
    "sales": 5
},
{
    "product_id": 123,
    "date": "2020-01-02",
    "sales": 6
},
{
    "product_id": 123,
    "date": "2020-01-03",
    "sales": 2
},
{
    "product_id": 123,
    "date": "2020-01-05",
    "sales": 88
}

Please note, that days without any sales are not in the sales index/type.

Now:

I need to filter all products that have more/less than X sales in a user defined time period (and even able to filter sales = 0)
I need to sort asc/desc based on the sales in that time period (ascending would also mean that it should start with the products without sales).
I need the matching product doc also in that result
I should also be able to sort on other fields from the product index

How do i go ahead and do this? My background is from Solr and i would use streaming expressions for this.

UPDATE

I'm going to put a bounty on this. I found out that this could be achievable by using Aggregations and nest them.

If my data structure has to be different for this, please describe. Thanks in advance!

user_2694184 · Answer

Joining two indices is not possible in elasticsearch.

There are couple of options:

Join type - But it is not rdbms way
You can filter on sales index and query back with the results from sales query on product collection to get product details..

To sort missing values, you should use missing parameter. Refer

To get ranges, 3to10, more than 30 you should use [range aggregation](aggregation range query elasticsearch)

GET /_search
{
    "aggs" : {
        "sale_ranges" : {
            "range" : {
                "field" : "sales",
                "ranges" : [
                    { "to" : 10 },
                    { "from" : 10, "to" : 20 },
                    { "from" : 20 }
                ]
            }
        }
    }
}

You cannot get products without sales easily. To get that,

You need to get all the products from your product table
You need to get the products which are missing from sales table with the help of the above step.

How to sort by aggregate from another index/type

1 Answers