Anton
Reputation: 976
Bucket_script aggregation on filters aggregation over nested documents
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
]
}
Upvotes: 3
Views: 1341
Answers (1)
Joe - Check out my books
Reputation: 16923
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
}
}
}
}
}
Upvotes: 1
Related Questions
- Elasticsearch: How to aggregate from nested documents up to each main document with nested filtering?
- ElasticSearch: Nested buckets aggregation
- Aggregation by nested objects with filter in ElasticSearch 6
- ElasticSearch query filter on nested documents
- ElasticSearch Nested Filter and Aggregation
- Nested Aggregation Elasticsearch
- Filtered nested aggregation in ElasticSearch?
- How to aggregate sub buckets of each bucket on nested documents
- How can I aggregate filtered nested documents in ElasticSearch?
- Nested aggregation and filter in ElasticSearch