Aggregations by combining in/out datetimes to get inventory over time

Elastic Stack Elasticsearch
1

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 :slight_smile:

2

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