Aggregation to get only the newest document for a product

[ly_maur]

Hey there,

we want to track the inventory of our products with Elasticsearch and Kibana. We are currently doing a prototype for that and are struggling with Kibana.

The situation:
We have different warehouses. Each product can have inventory in each warehouse.
We get new inventory data daily. These data are only of products where the inventory changed.
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:

{
	"sku": "super_shirt_XL_green",
	"product": "super_shirt"
	"skuInventoryDate": "2018-06-26T11:32:07",
	"warehouse": "warehouse A",
	"inventory": 15
}

{
	"sku": "super_shirt_XL_red",
	"product": "super_shirt"
	"skuInventoryDate": "2018-06-26T05:35:04",
	"warehouse": "warehouse B"
	"inventory": 20
}

The problem:
If you want to get the newest inventory for every product, you can do that with Elasticsearch easily. Just do an aggregation that only selects the newest document for every SKU.
But how can you do that with Kibana? I could not find a way to e. g. sum up the newest inventory of every product in a warehouse to get a full inventory sum of all products for that warehouse.

I cannot believe this is not possible in Kibana, but neither could I find a way to solve this problem.

[Christian_Dahlqvist]

Have you considered storing the latest version of each entity in a separate index? The document ID could be a concatenation of SKU and warehouse ID. As soon as you get a new entry you update this index with the correct value so it always holds the latest state. If you the are looking to plot inventory over time you use the existing index, and if you are looking for latest info you base your visualisation on the new index.

This way you trade doing a bit extra work at index time (index or update into the new index) for simpler and faster queries.

[ly_maur]

Yes I thought about creating a "current" index with the newest stock for each product.
But we also want to check what the inventory in warehouse A looked like at a certain point in the past, e. g. one week ago or at the 2018-02-12.
If you look with Kibana only at the 2018-02-12, you only see the inventory of the products where the inventory changed on that day. Is there some way in Kibana to get the newest document for each SKU and warehouse?
Concrete example: We want to sum up the inventory of all products which were in stock in warehouse A on the 2018-02-12 at 1pm. How do I do this in Kibana?
Or is this not possible with Kibana alone and I have to find a workaround in Elasticsearch?