When using the date histogram with an embedded array to create a histogram for displaying used discounts per day, I have the problem that when a customer has multiple subscriptions the counted discount is counted for all subscriptions even when the createdAt is different.
{
"aggs": {
"histogram": {
"aggs": {
"counter": {
"terms": {
"field": "subscriptions.discounts.couponCodeKey.keyword",
"missing": "0",
"size": 1000
}
}
},
"date_histogram": {
"extended_bounds": {
"max": "2022-07-19T21:38:55.3506091Z",
"min": "2020-12-31T23:00:00Z"
},
"field": "subscriptions.createdAt",
"calendar_interval": "year",
"time_zone": "Europe/Zurich"
}
}
},
"query": {
"range": {
"subscriptions.createdAt": {
"gte": "2020-12-31T23:00:00Z"
}
}
},
"size": 0,
"sort": [
{
"subscriptions.createdAt": {
"order": "asc"
}
}
]
}
The sample object looks as follows:
{
"Subscriptions": [
{
"Id": "c211ff01-3720-4ad6-99a3-b923696e4f1c",
"CreatedAt": "2022-06-20T18:38:31.403Z",
"Discounts": [
{
"CouponCodeKey": "cash"
}
]
},
{
"Id": "df7fd661-b07a-4001-b9a6-6c784deca706",
"CreatedAt": "2022-07-05T08:00:00Z",
"Discounts": null
}
],
"CreatedAt": "2022-06-20T18:37:38.362Z"
}
This creates this wrong result:
"aggregations" : {
"date_histogram#histogram" : {
"buckets" : [
{
"key_as_string" : "2022-06-20T00:00:00.000+02:00",
"key" : 1655676000000,
"doc_count" : 1,
"sterms#counter" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "cash",
"doc_count" : 1
}
]
}
},
{
"key_as_string" : "2022-07-05T00:00:00.000+02:00",
"key" : 1656972000000,
"doc_count" : 1,
"sterms#counter" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 0,
"buckets" : [
{
"key" : "cash",
"doc_count" : 1
}
]
}
}
]
}
}
The discount from the first subscription at 20.06.2022 was also counted towards the 05.07.2022 subscription, this should not happen.
Thanks in advance for any help!