Is there a way to search for the document when the user_id first appeared?

Elastic Stack Elasticsearch
1

Hi
I have an index recording nginx logs, one log-line matches one es document.

I'd like to know if there is a way to search for the the document when the user_id first appeared.

To be clear, I'm not asking how to find the document of one specified user_id, but all user_ids.

# not like this!
{
  "size": 1,
  "sort": [
    {
      "@timestamp": {
        "order": "asc"
      }
    }
  ],
  "query": {
    "match": {
      "user_id": "xxxxxx"
    }
  }
}

In other words, I need some filter to search for the first appearance documents distinct by user_id, and then do some aggregations on this sub-records.

Thank you for your help!

2

This is not possible.

3

Even with the groovy script?

4

Your best option today would be to run a terms aggregation on user_id with a sum top_hits aggregation that sorts by timestamp, but this won't scale well since user_id has a high cardinality I suppose.

5

And it seemed that neither can I do a furthermore pipeline aggregation on top_hits aggregations.