Elasticsearch query with time range and cardinality

Question

I need help with ES query for both Time Range and Cardinality. For now, my query for Time Range is as follow:

    query={
      "query": {
        "bool": {
          "must": [
            {
              "query_string": {
                "query": querystr_var,
                "analyze_wildcard": "true"
              }
            }
          ]
        }
      },
      "size": 0,
      "_source": {
        "excludes": []
      },
      "aggs": {
        "range": {
          "date_range": {
            "field": timeField_var,
            "format" : "yyyy-MM-dd HH:mm:ss.SSS",
            "ranges": [
              {
                "from": startDateTime_var,
                "to": endDateTime_var,
                "key": "CurrentCount"
              },
              {
                "from": prev1WeekStartDateTime_var,
                "to": prev1WeekEndDateTime_var,
                "key": "Prev1WeekCount"
              }
            ],
            "keyed": "true"
          }
        }
      }
    }

The above query is work fine, but now I need to also count for unique "CustomerID" using cardinality, I tried below but the result is the same as before, no effect:

    query={
      "query": {
        "bool": {
          "must": [
            {
              "query_string": {
                "query": querystr_var,
                "analyze_wildcard": "true"
              }
            }
          ]
        }
      },
      "size": 0,
      "_source": {
        "excludes": []
      },
      "aggs": {
        "session_count": {
            "cardinality": {
                "field": "CustomerID"
            }
        }, 
        "range": {
          "date_range": {
            "field": timeField_var,
            "format" : "yyyy-MM-dd HH:mm:ss.SSS",
            "ranges": [
              {
                "from": startDateTime_var,
                "to": endDateTime_var,
                "key": "CurrentCount"
              },
              {
                "from": prevWeekStartDateTime_var,
                "to": prevWeekEndDateTime_var,
                "key": "PrevWeekCount"
              }
            ],
            "keyed": "true"
          }
        }
      }
    }

Can you please help with this query. Thanks so much!

Sorry let me make it clear, we need to calculate the unique number of "CustomerID" for each of the ranges. That is, the value of "CurrentCount" and the "PrevWeekCount" are for unique "CustomerID". Hope that make sense. Thanks. — TD JSmith, Dec 13 '22 at 15:36

score 0 · Answer 1 · answered Dec 13 '22 at 05:38

Your query seems to be correct. I tried a similar query (with aggregation), with some sample data, and the result is as expected.

Index Data:

{
    "date": "2015-01-01",
    "customerId": 1
}
{
    "date": "2015-02-01",
    "customerId": 2
}
{
    "date": "2015-03-01",
    "customerId": 3
}
{
    "date": "2015-04-01",
    "customerId": 3
}

Search Query:

{
    "size":0,
    "aggs": {
        "session_count": {
            "cardinality": {
                "field": "customerId"
            }
        }, 
        "range": {
            "date_range": {
                "field": "date",
                "ranges": [
                    {
                        "from": "2015-01-01",
                        "to": "2015-05-01"
                    }
                ],
                "keyed": "true"
            }
        }
    }
}

Search Result:

"aggregations": {
        "session_count": {
            "value": 3
        },
        "range": {
            "buckets": {
                "2015-01-01T00:00:00.000Z-2015-05-01T00:00:00.000Z": {
                    "from": 1.4200704E12,
                    "from_as_string": "2015-01-01T00:00:00.000Z",
                    "to": 1.4304384E12,
                    "to_as_string": "2015-05-01T00:00:00.000Z",
                    "doc_count": 4
                }
            }
        }
    }

Sorry let me make it clear, we need to calculate the unique number of "CustomerID" for each of the ranges. That is, the value of "CurrentCount" and the "PrevWeekCount" are for unique "CustomerID". Hope that make sense. Thanks. — TD JSmith, Dec 13 '22 at 15:37

score 0 · Answer 2 · answered Dec 14 '22 at 03:27

Ok so after losing lots of hair, I found out that I need to put the "cardinality" under each of the separated date_range, something like this:

...

"aggs": {
        "currentCount": {
          "date_range": {
                "field": timeField_var,
                "format" : "yyyy-MM-dd HH:mm:ss.SSS",
                "ranges": [
                    {
                        "from": startDateTime_var,
                        "to": endDateTime_var,
                        "key": "CurrentCount"
                    }   
                ],
                "keyed": "true"
            },
                "aggs": {
                    "currentUnique": {
                        "cardinality": {
                            "field": "CustomerID"
                        }
                    }
                }
        },
        "previousCount": {
          "date_range": {
                "field": timeField_var,
                "format" : "yyyy-MM-dd HH:mm:ss.SSS",
                "ranges": [
                    {
                        "from": prevWeekStartDateTime_var,
                        "to": prevWeekEndDateTime_var,
                        "key": "previousUnique"
                    }   
                ],
                "keyed": "true"
            },
            "aggs": {
                    "previousUnique": {
                        "cardinality": {
                            "field": "CustomerID"
                        }
                    }
                }
}

Elasticsearch query with time range and cardinality

2 Answers2