Elasticsearch - How to get the latest record in each group with filter?-CodePudding

I have a few records in elasticsearch I want to group the record by user_id and fetch the latest record which event_type=1 If latest record event_type value is not 1 then we should not fetch that record. I did it in MySQL query. Please let me know how can I do that same in elasticsearch?

After execute MySQL query

SELECT * FROM user_events
     WHERE id IN( SELECT max(id) FROM `user_events` group by user_id ) AND event_type=1;

I need the same output in elasticseatch.

CodePudding user response：

GET user_events/_search
{
  "size": 1,
  "query": {
    "term": {
      "event_type": 1
      }
    },
  "sort": [
    {
      "id": {
        "order": "desc"
      }
    }
  ]
}

Explanation: This is an Elasticsearch API request in JSON format. It retrieves the latest event of type 1 (specified by "event_type": 1 in the query) from the "user_events" index, with a size of 1 (specified by "size": 1) and sorts the results in descending order by the "id" field (specified by "order": "desc" in the sort).

CodePudding user response：

If your ES version supports, you can do it with field collapse feature. Here is an example query:

{
  "_source": false,
  "query": {
    "bool": {
      "filter": {
        "term": {
          "event_type": 1
        }
      }
    }
  },
  "collapse": {
    "field": "user_id",
    "inner_hits": {
      "name": "the_record",
      "size": 1,
      "sort": [
        {
          "id": "desc"
        }
      ]
    }
  },
  "sort": [
    {
      "id": {
        "order": "desc"
      }
    }
  ]
}

In the response, you will see that the document you want is in inner_hits under the name you give. In my example it is the_record. You can change the size of the inner hits if you want more records in each group and sort them.