Hey there,
we want to track the inventory of our products with Elasticsearch and Kibana and are building a prototype for that.
I am struggeling to build a query for this use case:
- We want to see how many (and which) products are in stock for warehouse A, but not for warehouse B.
- We want to see how many (and which) products are in stock for both warehouses.
- We want to see these numbers for any point in time. (E. g.: now, 7 days ago, etc.)
The situation:
We have different warehouses. Each product can have inventory in each warehouse. Some products don't have inventory in some warehouses.
We get new inventory data continuously. These data are only from products where the inventory changed. This means we don't get a full inventory export of all products every day.
The inventory numbers are absolute. This means we get messages like "The inventory of product A is at 50 now" and not "The inventory of product A changed by -10".
For each new inventory message we have a new document. This is what a document looks like. (The design of the document is just a draft and can be changed.):
{
"sku": "super_shirt_XL_green",
"product": "super_shirt"
"skuInventoryDate": "2018-06-26T05:37:07",
"warehouse": "warehouse A",
"inventory": 15
}
{
"sku": "super_shirt_XL_red",
"product": "super_shirt"
"skuInventoryDate": "2018-06-26T05:37:07",
"warehouse": "warehouse B"
"inventory": 20
}
Has anyone an idea how a query for the above mentioned use cases could look like?
To get only the newest inventory for an SKU, I would use the top hits aggregation. Then I would do a terms aggregation on the warehouse. Now I have a bucket for each warehouse, with each bucket containing the newest inventory count for each SKU.
But here I am stuck. Now I have to compare the documents of the first bucket with the documents of the second bucket. The SKUs which are in one bucket only exist in that warehouse, and those which are in both buckets have stock in both warehouses. How do I find that out with an Elastic-query?