Can't Filter Aggregation Results

Hello, all!

I have an index with web transaction logs. It consists of requests and responses.
An index looks like this:

{ request_id: '1234', message: 'request', path: '/fare' }
{ request_id: '1234', message: 'response', status: 202 }
{ request_id: '5789', message: 'request', path: '/orders' }
{ request_id: '5789', message: 'response', status: 202 }
{ request_id: '4512', message: 'request', path: '/orders' }
{ request_id: '4512', message: 'response', status: 500 }

I need to count the number of failed (status != 202) '/orders' requests. But I can't figure out how to write a query.

I grouped requests-responses by request_id but then couldn't find out how to filter aggregation results by request's path and response's status. Also, I tried to find something in Pipeline Aggregation. There is a Bucket Selector Aggregation in docs, but the problem is that it works only with numeric metrics. So, I'm stuck(

Is it possible to achieve my goal at all? If so, can you give me a direction, please?
Or maybe it would be better to reconsider index design?

Thanks in advance!

You can use a Boolean query. must_not section to eliminate status=202, filter section to match message=response and path=/orders.
Since you only want count, you can hit the _count endpoint instead of _search.

Hello, Glen. Thank you for the response. I appreciate your help! If I get your suggestion correctly, then I can't apply it to my problem. Let me explain why.

My task is to count failed transactions for a specific path. One transaction produces two log entries: request and response. A request entry contains :path field and response has :status field. Because these fields are placed in separate entities, I can't simultaneously filter section with match message=response and path=/orders. I need to aggregate it first. But when I aggregate requests and responses into buckets by request_id (one bucket == one transaction) I lose immediate access to :path and :status fields.

I've recently *tried to achieve a part of desired result with next steps:

  1. Aggregate requests and responses into buckets by :request_id
  2. Add :path attribute to newly formed buckets using scripted_metric
  3. Filter resulted buckets using Bucket Selector Aggregation

But this didn't work out. Resulting response contained empty :buckets field.

So, my questions are still stand:

  1. Is it possible to achieve my goal at all?
  2. Maybe I should reconsider index design? Merge request and responses into single entity, for example.

*my failed attempt

GET logs/_search
  "size": 0,
  "aggs": {
    "request_id": {
      "terms": { "field": "request_id" },
      "aggs": {
        "path": {
          "scripted_metric": {
            "map_script": """
              if (doc['path'].size() != 0) {
                state.payload = doc['path'].value;
            "combine_script": "return state.payload;",
            "reduce_script": "return states[0] == '/orders' ? 1 : 0;"
        "bucket_filter": {
          "bucket_selector": {
            "buckets_path": {
              "path": "path.value"
            "script": "params.path == 1"

P.S.: And sorry for such verbosity, I'm just trying to express myself as clear as I can.

Sorry, yeah, I should have noticed the requests and responses are in distinct docs.

(The detail and clarity - "verbosity" - is always much appreciated.)

Yes, it would be a very good idea to merge these into a single document. The scenario you're trying to accomplish is resounding testimony to the benefit. Elasticsearch's join-like capabilities are limited to nested documents and parent/child; with this data set, just merging the response into the request document is sufficient.

If you stand up a Kibana instance, you can load sample data. One of the sample data sets is Web Logs, which you'll find is supported by a single index, in which the documents contain all the request info plus the response.

1 Like

Wow! This sample data is really great! Thank you for your help! Amazing support.

1 Like

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