Elasticsearch date histogram aggregation with embedded array carries values over when multiple entries

Viewed 22

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!

0 Answers
Related