How to filter aggregation results in Elasticsearch?

Question

I have an Elasticsearch index "sessions" with two fields:

  "user_id" : {
    "type" : "keyword"
  },
  "login_at" : {
    "type" : "date"
  }

Every time a user logs in, a new record is created with user_id and current timestamp.

I want to list all users who have not logged in for a week. I know how to get the last login time for each user with:

GET sessions/_search
{
  "size": 0,
  "aggs": {
    "user_aggs": {
      "terms": {
        "field": "user_id",
        "order": {
           "last_access": "asc"
        }
      },
      "aggs": {
        "last_access": {
          "max": {
            "field": "login_at"
          }
        }
      }
    }
  }
}

The above query lists all users and their last login time.

How can I filter the "last_access" field to values that are smaller than now-7d?

user2101712 · Accepted Answer · 2020-09-06 18:13:22Z

Ok, I was able to resolve this. Here is the query:

GET sessions/_search
{
  "size": 0,
  "aggs": {
    "user_aggs": {
      "terms": {
        "field": "user_id",
        "size": 1000,
        "order": {
           "last_access": "asc"
        }
      },
      "aggs": {
        "last_access": {
          "max": {
            "field": "login_at"
          }
        },
        "users_filtered": {
          "bucket_selector": {
            "buckets_path": {
              "key": "last_access"
            },
            "script": "params.key < a_timestamp"
          }
        }
      }
    }
  }
}

The a_timestamp variable has to be sent from the application, but that is ok.

Collectives™ on Stack Overflow

How to filter aggregation results in Elasticsearch?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related