I have several inventory documents stored in elasticsearch, here is a simplified example (dates in database format YYYY-MM-DD
):
[
{"item_id": "a1", "in": "2020-12-01", "out": "2021-02-03"},
{"item_id": "b2", "in": "2021-01-05", "out": "2021-03-05"},
{"item_id": "c3", "in": "2021-02-06", "out": "2021-02-20"},
{"item_id": "d4", "in": "2021-03-04", "out": null},
{"item_id": "e5", "in": "2021-01-08", "out": "2021-05-03"},
{"item_id": "f6", "in": "2021-05-10", "out": null},
{"item_id": "g7", "in": "2021-05-04", "out": null},
{"item_id": "h8", "in": "2021-02-06", "out": "2021-04-03"}
]
- The property
in
represents the date when the item was added to the inventory - The property
out
represents the date when the item was removed from the inventory
I would like to calculate a histogram-like overview of the inventory items over time, grouped by month. The overview will contain the number of items satisfying the following condition for each month (logical AND
between them):
-
in
should be less or equal to 1st day of the month -
out
should be greater or equal to 1st day of the month or null
Here a visual representation of the dataset:
item_id | 2021-01 | 2021-02 | 2021-03 | 2021-04 | 2021-05 | 2021-06 |
---|---|---|---|---|---|---|
a1 | o | o | ||||
b2 | o | o | ||||
c3 | ||||||
d4 | o | o | o | |||
e5 | o | o | o | o | ||
f6 | o | |||||
g7 | o | |||||
h8 | o | o |
What I would like to get in output is something that resembles this:
"buckets": [
{
"key": "2021-01",
"doc_count": 1,
},
{
"key": "2021-02",
"doc_count": 3,
},
{
"key": "2021-03",
"doc_count": 3,
},
{
"key": "2021-04",
"doc_count": 4,
},
{
"key": "2021-05",
"doc_count": 2,
},
{
"key": "2021-06",
"doc_count": 3,
}
]
Do you have any idea or advice on that? I don't think I can use the date histogram aggregation, or at least not without adding some conditions to it.
Thanks