Combine results of multiple aggregations

Question

I have movies index in which each document has this structure :

Document :

{
                    "color": "Color",
                    "director_name": "Sam Raimi",
                    "actor_2_name": "James Franco",
                    "movie_title": "Spider-Man 2",
                    "actor_3_name" : "Brad Pitt",
                    "actor_1_name": "J.K. Simmons"
}

I need to do calculate number of movies corresponding to each actor (actor can be in both actor_1_name or actor_2_name or actor_3_name field)

Mapping of these 3 fields is :

Mapping

"mappings": {
            "properties": {
                "actor_1_name": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "actor_2_name": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                 "actor_3_name": {
                "type": "text",
                "fields": {
                    "keyword": {
                        "type": "keyword",
                        "ignore_above": 256
                    }
                }
            }
       }
}

Is there a way I can aggregated result which can combine terms from all 3 actor fields and give a single aggreagation.

Currently I am creating separate aggregation for each actor field and through my JAVA code combine these different aggregations into one.

Search Query by creating different aggregation :

Search Query :

{
    "aggs" : {
        "actor1_count" : {
            "terms" : {
                "field" : "actor_1_name.keyword"
            }
        },
        "actor2_count" : {
            "terms" : {
                "field" : "actor_2_name.keyword"
            }
        },
        "actor3_count" : {
            "terms" : {
                "field" : "actor_3_name.keyword"
            }
        }
    }
}

Result

Sample Result is :

"aggregations": {
"actor1_count": {

            "buckets": [

                {
                    "key": "Johnny Depp",
                    "doc_count": 2
                }
            ]
},

"actor2_count": {

            "buckets": [
                {
                    "key": "Johnny Depp",
                    "doc_count": 1                   }
      ]
},
"actor3_count": {

            "buckets": [

                {
                    "key": "Johnny Depp",
                    "doc_count": 3
                }

           ]
    }
 }

So, is it possible instead of creating different aggregation , I can combine result of all 3 aggregation in one aggreation through Elasticsearch.

Basically this is I want :

"aggregations": {
    "actor_count": {

                "buckets": [

                    {
                        "key": "Johnny Depp",
                        "doc_count": 6
                    }
                ]
    }
}

(Johnny Depp doc_count should show sum from all 3 field actor_1_name, actor_2_name, actor_3_name wherever it is present)

I have tried though script but it dint worked correctly .

Script Query :

{
    "aggregations": {
        "name": {
            "terms": {
                "script": "doc['actor_1_name.keyword'].value + ' ' +  doc['actor_2_name.keyword'].value + ' ' + doc['actor_2_name.keyword'].value"
            }
        }
    }
}

It is combining actor names and then giving result .

Result :

"buckets": [

                {
                    "key": "Steve Buscemi Adam Sandler Adam Sandler",
                    "doc_count": 6
                },
                {
                    "key": "Leonard Nimoy Nichelle Nichols Nichelle Nichols",
                    "doc_count": 4
                }

            ]

Answer 1

This is not going to work w/ terms. Gotta resort to scripted_metric, I think:

GET actors/_search
{
  "size": 0,
  "aggs": {
    "merged_actors": {
      "scripted_metric": {
        "init_script": "state.actors_map=[:]",
        "map_script": """
          def actor_keys = ['actor_1_name', 'actor_2_name', 'actor_3_name'];

          for (def key : actor_keys) {

            def actor_name = doc[key + '.keyword'].value;

            if (state.actors_map.containsKey(actor_name)) {
              state.actors_map[actor_name] += 1;
            } else {
              state.actors_map[actor_name] = 1; 
            }
          }
        """,
        "combine_script": "return state",
        "reduce_script": "return states"
      }
    }
  }
}

yielding

...
"aggregations" : {
    "merged_actors" : {
      "value" : [
        {
          "actors_map" : {
            "Brad Pitt" : 5,
            "J.K. Simmons" : 1,
            "James Franco" : 3
          }
        }
      ]
    }
  }