Count results on filtered and sorted query

Elastic Stack Elasticsearch
1

I'm having troubles doing this equivalent in ES:

SELECT COUNT(*)
FROM
(
    SELECT current_place
    FROM `request`
    WHERE user_id = '3'
    ORDER BY asked_at DESC
    LIMIT 10
) sr1
WHERE current_place = '4'

The goal is to take the 10 most recent records for an user (asked_at is a timestamp field), and count how many record have a current_place = '4'

In Elasticsearch I did this, without ordering because I didn't even succeed to filter to 10 elements:

GET /index/type/_search
{
  "size": 10,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "user_id": 3
          }
        },
        {
          "term": {
            "current_place": 4
          }
        }
      ]
    }
  }
}

Which gives me:

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 54,
    "max_score" : 0.0,
    "hits" : [
      ... truncated, 10 records ...
    ]
  }
}

How can I perform a count on the ordered and filtered data ?

2

You must use sort for sorting the records according to asked_at.

In order to get the count, you should run value count aggregation.

3

How to apply the value count aggregation to just the 10 first records ?

4

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.