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!

CodePudding user response：

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
                }
            }
        }
    }

CodePudding user response：

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"
                        }
                    }
                }
}