Problem with aggregation returning most popular array entry among all documents

Elastic Stack Elasticsearch
1

Please help me solve aggregation issue:

Lets say I have mapping

(...)
"properties": {
    "kidName": {
        "type": "keyword",
        "fields": {
            "txt": {
                "type": "text",
            }
        }
    },
    "eatenFruit": {
        "type": "nested",
        "properties": {
            "fruitName": {
                "type": "keyword",
                "fields": {
                    "txt": {
                        "type": "text"
                    }
                }
            }
        }
    }
}
(...)

And want to achieve query that will give me 20 most popular fruits among all children.

I've came up with something like this:

(...)
"aggs": {
        "ListOfMostPopularFruits": {
            "nested": {
                "path": "eatenFruit"
            },
            "aggs": {
                "Sorted": {
                    "terms": {
                        "field": "eatenFruit.fruitName",
                        "size": 20,
                        "order": [
                            {
                                "_count": "desc"
                            },
                            {
                                "_key": "asc"
                            }
                        ]
                    }
                }
            }
        }
    }
(...)

but this is not what I want. Because if let's say Billy has eaten 20 apples, Mandy has eaten 2 bananas and Johny has eaten 5 bananas. This will return:

(...)
"aggregations": {
        "ListOfMostPopularFruits": {
            "doc_count": 123456,
            "Sorted": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 123,
                "buckets": [
                    {
                        "key": "apple",
                        "doc_count": 20
                    },
                    {
                        "key": "banana",
                        "doc_count": 7
                    }
                ]
            }
        }
    }
(...)

and I need aggregation that will give me something like:

(...)
"aggregations": {
        "ListOfMostPopularFruits": {
            "doc_count": 123456,
            "Sorted": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 123,
                "buckets": [
                    {
                        "key": "banana",
                        "doc_count": 2
                    },
                    {
                        "key": "apple",
                        "doc_count": 1
                    }
                ]
            }
        }
    }
(...)

My limitations:

I'm limited to version 7.10 and I cannot denormalize data more than it is (the only thing I can do is get rid of nested type).

Can any1 help me with solving this issue?

2

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