3

I have my documents, and they contain nested "events" (like clicks on a website) documents.

Now I want to calculate the ration between nested events with name=x and nested events with name=y

This is my query:

curl -XGET http://192.168.0.206:9200/user-data/_search?pretty -H 'Content-Type: application/json' -d '{
    "query": { ... },
    "aggs": {
        "conversation_clicks": {
            "aggs": {
              "bucket-aggregation-for-clicks-per-conversation": {
                "filters": {
                  "filters": {
                    "presentation-clicks": { "term" : { "events.name" : "presentation-custom-anchor" }},
                    "conversation-starts": { "term" : { "events.name" : "conversation-start" }}
                  }
                }
              }, 
              "clicks-per-conversation": {
                "bucket_script": {
                  "buckets_path": {
                    "my_clicks": "bucket-aggregation-for-clicks-per-conversation['presentation-clicks']>_count", 
                    "my_conversations": "bucket-aggregation-for-clicks-per-conversation['conversation-starts']>_count"
                  },
                  "script": "params.my_clicks / params.my_conversations"
                }
              }
            },
            "nested": {
                "path": "events"
            }
        }
    },
    "size": 1
}

So first I create two buckets of the nested events (called "presentation-click" and "conversation-starts") This is working fine. Now I want to divide the number of nested docs in the first bucket by the number of nested docs in the seconds bucket.

If I try this, I get this error:

{
  "error" : {
    "root_cause" : [ ],
    "type" : "search_phase_execution_exception",
    "reason" : "",
    "phase" : "fetch",
    "grouped" : true,
    "failed_shards" : [ ],
    "caused_by" : {
      "type" : "class_cast_exception",
      "reason" : "class org.elasticsearch.search.aggregations.bucket.nested.InternalNested cannot be cast to class org.elasticsearch.search.aggregations.InternalMultiBucketAggregation (org.elasticsearch.search.aggregations.bucket.nested.InternalNested and org.elasticsearch.search.aggregations.InternalMultiBucketAggregation are in unnamed module of loader 'app')"
    }
  },
  "status" : 503
}

I tried to do the same calculation on non-nested documents but it also did not work. It does not raise an error, but it just doesn't do the calculation. (see my other question: Bucket_script aggregation on filters aggregation not showing results )

Can anyone help me understanding what the error message exactly means and how to fix my query so I get my desired result?

Thanks a lot,

Anton

PS: this is how a document with nested documents looks like in my index:

{
  "_index": "user-data",
  "_type": "_doc",
  "_id": "479951",
  "_version": 24,
  "_score": null,
  "_source": {
    "user_code": "1060",
    "team_leader": 146,
    "assistant_coach": [],
    "campaign_id": 155,
    "user_name_search": "H., Helga",
    "petitions": [],
    "team_id": 17229,
    "unfinished_donations": [],
    "campaign_name": "Puppy Love",
    "team_name": "Manfred Meier für Puppy Love",
    "id": 479951,
    "customer_name": "Demo",
    "campaign_type": null,
    "workshift_id": 7,
    "coach_id": 10020,
    "coach_name": "H., Helga",
    "campaign_is_online": true,
    "commission": null,
    "customer_id": 9,
    "events": [
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:18:55.983224+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 725,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:19:37.983224+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 726,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-start",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:19:54.983224+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 727,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-custom-anchor",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:20:31.983224+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 728,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "campaign-start-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:21:09.983224+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": "abort",
        "id": 729,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-end",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": 1.5333333333333334,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:09:14.508422+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 730,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:09:38.508422+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 731,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-start",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:10:08.508422+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 732,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-custom-anchor",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:10:47.508422+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 733,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "campaign-start-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T10:11:09.508422+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": "abort",
        "id": 734,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-end",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": 1.5166666666666666,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T19:25:35.887239+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 735,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T19:26:01.887239+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 736,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-start",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T19:26:41.887239+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 737,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-custom-anchor",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T19:27:03.887239+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 738,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "campaign-start-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T19:27:26.887239+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": "abort",
        "id": 739,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-end",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": 1.4166666666666667,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:37:28.683193+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 740,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:37:59.683193+00:00",
        "event_context_source_category": "campaign-start",
        "event_context_reason": null,
        "id": 741,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-start",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:38:48.683193+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 742,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-custom-anchor",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:39:13.683193+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 743,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-custom-anchor",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:39:39.683193+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 744,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "presentation-custom-anchor",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:40:21.683193+00:00",
        "event_context_source_category": "presentation",
        "event_context_reason": null,
        "id": 745,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "campaign-form-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:41:09.683193+00:00",
        "event_context_source_category": "campaign-form",
        "event_context_reason": "abort",
        "id": 746,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "conversation-end",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": 3.1666666666666665,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      },
      {
        "event_type": "click",
        "timestamp": "2020-04-09T18:41:46.683193+00:00",
        "event_context_source_category": "campaign-form",
        "event_context_reason": null,
        "id": 747,
        "event_context_target_id": "b3542fe3-4898-4bb3-9cb0-99a06845230e",
        "name": "campaign-start-open",
        "event_location_referrer": "/next/forms/13/start",
        "event_location_current": "/next/forms/123/form/page/page-1",
        "event_context_duration": null,
        "event_context_tag_name": "next-info-material-link",
        "event_context_source_id": "123"
      }
    ],
    "user_id": 10020,
    "campaign_name_search": "Puppy Love",
    "tags": [],
    "job_applications": [],
    "tracked_hours": 0,
    "weight": 2,
    "hours": 12,
    "campaign_manager": [
      25873
    ],
    "user_name": "H., Helga",
    "date": "2020-04-09",
    "team_type": null,
    "name": "Ganztag",
    "team_leader_name": "S., Manfred",
    "presentations": [],
    "points": 0,
    "donations": []
  },
  "fields": {
    "date": [
      "2020-04-09T00:00:00.000Z"
    ]
  },
  "sort": [
    1586390400000
  ]
}
Improve this question
2
  • Lemme see a sample of your docs so it's easier to replicate. Commented Apr 13, 2020 at 15:36
  • Hey @jzzfs Thanks for you quick reply. I have added a sample document from my index. Commented Apr 14, 2020 at 16:07

1 Answer 1

Reset to default
1

The only way I know to get rid of the InternalNested error is to filter w/ nested queries & perform nested terms aggs instead of using the filters aggs. It's confusing but the two are roughly the same. That way, you're isolating the nested contexts and providing the bucket_script with already resolved count paths.

{
  "aggs": {
    "all_my_documents": {
      "filters": {
        "filters": {
          "all": {
            "match_all": {}
          }
        }
      },
      "aggs": {
        "presentation-clicks": {
          "filter": {
            "nested": {
              "path": "events",
              "query": {
                "term": {
                  "events.name": "presentation-custom-anchor"
                }
              }
            }
          },
          "aggs": {
            "value_count_parent": {
              "nested": {
                "path": "events"
              },
              "aggs": {
                "value_count": {
                  "value_count": {
                    "field": "events.name"
                  }
                }
              }
            }
          }
        },
        "conversation-starts": {
          "filter": {
            "nested": {
              "path": "events",
              "query": {
                "term": {
                  "events.name": "conversation-start"
                }
              }
            }
          },
          "aggs": {
            "value_count_parent": {
              "nested": {
                "path": "events"
              },
              "aggs": {
                "value_count": {
                  "value_count": {
                    "field": "events.name"
                  }
                }
              }
            }
          }
        },
        "clicks-per-conversation": {
          "bucket_script": {
            "buckets_path": {
              "my_clicks": "presentation-clicks>value_count_parent>value_count",
              "my_conversations": "conversation-starts>value_count_parent>value_count"
            },
            "script": "params.my_clicks / params.my_conversations"
          }
        }
      }
    }
  },
  "size": 0
}

yielding

"aggregations" : {
    "all_my_documents" : {
      "buckets" : {
        "all" : {
          "doc_count" : 2,
          "conversation-starts" : {
            "doc_count" : 2,
            "value_count_parent" : {
              "doc_count" : 4,
              "value_count" : {
                "value" : 4
              }
            }
          },
          "presentation-clicks" : {
            "doc_count" : 1,
            "value_count_parent" : {
              "doc_count" : 2,
              "value_count" : {
                "value" : 2
              }
            }
          },
          "clicks-per-conversation" : {
            "value" : 0.5
          }
        }
      }
    }
  }
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This looks promising! Thanks! I tried it but it filters away all events in the "presentation-clicks" and "conversation-starts" sub aggregations. Need to play around with this more in the evening!
Cool. Isn't it supposed to filter precisely those two?
If you look at the sample document in the questsion. There are n nested "events" documents. Some of them have a name="presentation-custom-anchor". I want the count of them. Some have a name="conversation-start". I want the count of them.
Now I get your aggs better! It seems to work, but as "value_count" value I get 23 for both types of events. So it always counts all the events sub documents. Need to play a little more with it to count only the filtered events.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.