ElasticSearch - Aggregations with document details

Question

I need to aggregate the following documents:

{
    "title": "American Psycho",
    "releaseDate": "7/06/2000",
    "imdbRate": "7.6",
    "casting": [
        {
            "name": "Christian Bale",
            "category": "Actor"
        },
        {
            "name": "Justin Theroux",
            "category": "Actor"
        }
    ]
}

{
    "title": "The Dark Knight",
    "releaseDate": "13/08/2008",
    "imdbRate": "9.0",
    "casting": [
        {
            "name": "Christian Bale",
            "category": "Actor"
        },
        {
            "name": "Morgan Freeman",
            "category": "Actor"
        }
    ]
}

by actor, and would like to get the following structure:

 [
    {"name": "Christian Bale"},
    {"movies": [
        {
            "title": "American Psycho",
            "releaseDate": "7/06/2000",
            "imdbRate": "7.6"
        },
        {
            "title": "The Dark Knight",
            "releaseDate": "13/08/2008",
            "imdbRate": "9.0"
        }, ...
]

Beyong using a standard term aggregation based on the casting.name field, how can I retrieve the releaseDate and imdbRate of the related documents? For each actor, I also need movies to be sorted by releaseDate asc.

Can I perform this using one single request?

Answer 1

As you have an array of casting objects in your documents you'll need to use the nested type in your mapping. To get the aggregations you want you need a combination of Terms Aggregations, Nested Aggregations and Reverse Nested Aggregations. Below is an example.

Create and index with the mapping:

POST /test
{
    "mappings": {
        "movie": {
            "properties": {
                "title": {
                    "type": "string",
                    "fields": {
                        "raw": {
                            "type": "string",
                            "index": "not_analyzed"
                        }
                    }
                },
                "releaseDate": {
                    "type": "string",
                     "index": "not_analyzed"
                },
                "casting": {
                    "type": "nested",
                    "properties": {
                        "name": {
                            "type": "string",
                            "fields":{
                            "raw": {
                                "type": "string",
                                "index": "not_analyzed"
                            }
                            }
                        },
                        "category": {
                            "type": "string",
                            "fields":{
                                "raw": {
                                    "type": "string",
                                    "index": "not_analyzed"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Index the documents:

POST /test/movie/1
{
    "title": "American Psycho",
    "releaseDate": "7/06/2000",
    "imdbRate": "7.6",
    "casting": [
        {
            "name": "Christian Bale",
            "category": "Actor"
        },
        {
            "name": "Justin Theroux",
            "category": "Actor"
        }
    ]
}

POST /test/movie/2
{
    "title": "The Dark Knight",
    "releaseDate": "13/08/2008",
    "imdbRate": "9.0",
    "casting": [
        {
            "name": "Christian Bale",
            "category": "Actor"
        },
        {
            "name": "Morgan Freeman",
            "category": "Actor"
        }
    ]
}

And finally search:

POST /test/movie/_search?search_type=count
{
    "aggs": {
        "nested_path": {
            "nested": {
                "path": "casting"
            },
            "aggs": {
                "actor_name": {
                    "terms": {
                        "field": "casting.name.raw"
                    },
                    "aggs": {
                        "movies": {
                            "reverse_nested": {},
                            "aggs": {
                                "movie_title": {
                                    "terms": {
                                        "field": "title.raw"
                                    },
                                    "aggs": {
                                        "release_date": {
                                            "terms": {
                                                "field": "releaseDate"
                                            }
                                        },
                                        "imdbRate_date": {
                                            "terms": {
                                                "field": "imdbRate"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

The response for Christian Bale is:

{
    "key": "Christian Bale",
    "doc_count": 2,
    "movies": {
        "doc_count": 2,
        "movie_title": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": "American Psycho",
                    "doc_count": 1,
                    "release_date": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "7/06/2000",
                                "doc_count": 1
                            }
                        ]
                    },
                    "imdbRate_date": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "7.6",
                                "doc_count": 1
                            }
                        ]
                    }
                },
                {
                    "key": "The Dark Knight",
                    "doc_count": 1,
                    "release_date": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "13/08/2008",
                                "doc_count": 1
                            }
                        ]
                    },
                    "imdbRate_date": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "9.0",
                                "doc_count": 1
                            }
                        ]
                    }
                }
            ]
        }
    }
}