Elasticsearch. Terms aggregation on nested field with duplicated values

Question

I have some problem with nested aggregation in Elasticsearch. I have mapping with nested field:

POST my_index/ my_type / _mapping
{
    "properties": {
        "name": {
            "type": "keyword"
        },
        "nested_fields": {
            "type": "nested",
                "properties": {
                "key": {
                    "type": "keyword"
                },
                "value": {
                    "type": "keyword"
                }
            }
        }
    }
}

Then I add one document to index:

POST my_index/ my_type
{
    "name":"object1",
        "nested_fields":[
            {
                "key": "key1",
                "value": "value1"

            },
            {
                "key": "key1",
                "value": "value2"
            }
        ]
}

As you see, in my nested array I have two items, which have similar key field, but different value field. Then I want to make such query:

GET / my_index / my_type / _search
{
    "query": {
        "nested": {
            "path": "nested_fields",
                "query": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "nested_fields.key": {
                                    "value": "key1"
                                }
                            }
                        },
                        {
                            "terms": {
                                "nested_fields.value": [
                                    "value1",
                                    "value2"
                                ]
                            }
                        }
                    ]
                }
            }
        }
    },
    "aggs": {
        "agg_nested_fields": {
            "nested": {
                "path": "nested_fields"
            },
            "aggs": {
                "agg_nested_fields_key": {
                    "terms": {
                        "field": "nested_fields.key",
                            "size": 10
                    }
                }
            }
        }
    }
}

As you see, I want to find all documents, which have at least one object in nested_field array, with key property equal to key1 and one of provided values (value1 or value2). Then I want to group founded documents by nested_fields.key. But I have such response

{
    "took": 13,
        "timed_out": false,
            "_shards": {
        "total": 5,
            "successful": 5,
                "failed": 0
    },
    "hits": {
        "total": 1,
            "max_score": 0.87546873,
                "hits": [
                    {
                        "_index": "my_index",
                        "_type": "my_type",
                        "_id": "AVuLNXxiryKmA7VEwOfV",
                        "_score": 0.87546873,
                        "_source": {
                            "name": "object1",
                            "nested_fields": [
                                {
                                    "key": "key1",
                                    "value": "value1"
                                },
                                {
                                    "key": "key1",
                                    "value": "value2"
                                }
                            ]
                        }
                    }
                ]
    },
    "aggregations": {
        "agg_nested_fields": {
            "doc_count": 2,
                "agg_nested_fields_key": {
                "doc_count_error_upper_bound": 0,
                    "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "key1",
                                "doc_count": 2
                            }
                        ]
            }
        }
    }
}

As you see from the response, I have one hit (it is correct), but the document was counted two times in aggregation (see doc_count: 2), because it has two items with 'key1' value in nested_fields array. How can I get the right count in aggregation?

That is the right count, since each nested element is a document in its own right. So you really have two nested documents that have `key1` as their key and either `value1` or `value2` as their value. — Val, Apr 20 '17 at 12:17
does this help https://stackoverflow.com/a/27578607/7379424? — rethabile, Jun 05 '17 at 20:44

score 0 · Answer 1 · answered Apr 20 '17 at 12:34

You will have to use reverse_nested aggs inside the nested aggregation to return the aggregation count on root document.

{
    "query": {
        "nested": {
            "path": "nested_fields",
            "query": {
                "bool": {
                    "must": [{
                            "term": {
                                "nested_fields.key": {
                                    "value": "key1"
                                }
                            }
                        },
                        {
                            "terms": {
                                "nested_fields.value": [
                                    "value1",
                                    "value2"
                                ]
                            }
                        }
                    ]
                }
            }
        }
    },
    "aggs": {
        "agg_nested_fields": {
            "nested": {
                "path": "nested_fields"
            },
            "aggs": {
                "agg_nested_fields_key": {
                    "terms": {
                        "field": "nested_fields.key",
                        "size": 10
                    },
                    "aggs": {
                        "back_to_root": {
                            "reverse_nested": {
                                "path": "_source"
                            }
                        }
                    }
                }
            }
        }
    }
}

how, you want the count of parent/root doc. ok i understand till here 'As you see from the response, I have one hit (it is correct), but the document was counted two times in aggregation (see doc_count: 2), because it has two items with 'key1' value in nested_fields array. How can I get the right count in aggregation?' you have add some more info what you want to achieve — user3775217, Apr 20 '17 at 14:03

Elasticsearch. Terms aggregation on nested field with duplicated values

1 Answers1