# Write a query to aggregate by any fields in mapping

**URL:** <https://discuss.elastic.co/t/write-a-query-to-aggregate-by-any-fields-in-mapping/29964>\
**Category:** Elasticsearch\
**Created:** [September 25, 2015, 6:48am UTC](https://discuss.elastic.co/t/write-a-query-to-aggregate-by-any-fields-in-mapping/29964 "2015-09-25T06:48:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![android.kc](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/android.kc/32/58806_2.png) [@android.kc](https://discuss.elastic.co/u/android.kc)\
**Post date:** [September 25, 2015, 6:48am UTC](https://discuss.elastic.co/t/write-a-query-to-aggregate-by-any-fields-in-mapping/29964/1 "2015-09-25T06:48:02Z")

</div>

Tried to build a query with 3 levels aggregation by ANY fields in the mappings, the fields can be nested type or non-nested type as the mapping below. For example, we can pick [array\_A.a, array\_B.b, A] or [B, array\_B.a, array\_B.b] to generate a 3 levels aggregation.

Also note that the order of the fields does matter, since aggregate by [array\_A.a, array\_B.b, A] and [A, array\_A.a, array\_B.b] would have different results.

To aggregate by a nested type, we include "nested": {"path": "root\_B"}" in queries; for non-nested type, we may need to include "reverse\_nested" for a root level field if the parent is nested type.

When building a query based on 3 fields selected randomly, we needed to check if the parent field is a nested type and has the same path. For example, two queries below returned different results. Q1 returned expected result but Q2 didn't return any data in the deepest buckets[] since it tred to access the field array\_A.array\_A.b, which not exists at all. Besides checking if the parent field is a nested type, also need to know if "reverse\_nested" is needed. This requires quite a bit effort on implementation. It seems that the nested path is "relative path" between the parent & child. Can we use "absolute path" in "nested": {"path": "aPath"}} instead, to simplify the logic?

```
q1
curl -XPOST "http://localhost:9200/myindex/_search" -d '{
    "size":0,
    "aggs": {
        "aggName": {
            "terms": {
                "field": "A"
            },
            "aggs": {
                "nestedAggName": {
                    "nested": {
                        "path": "array_A"
                    },
                    "aggs": {
                        "appName": {
                            "terms": {
                                "field": "array_A.a"
                            },
                            "aggs": {
                                "appName": {
                                    "terms": {
                                        "field": "array_A.b"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}'

q2
curl -XPOST "http://localhost:9200/myindex/_search" -d '{
    "size":0,
    "aggs": {
        "aggName": {
            "terms": {
                "field": "A"
            },
            "aggs": {
                "nestedAggName": {
                    "nested": {
                        "path": "array_A"
                    },
                    "aggs": {
                        "appName": {
                            "terms": {
                                "field": "array_A.a"
                            },
                            "aggs": {
                                "nestedAggName": {
                                    "nested": {
                                        "path": "array_A"
                                    },
                                    "aggs": {
                                        "appName": {
                                            "terms": {
                                                "field": "array_A.b"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}'

"mappings": {
    "type": {
        "properties": {
            "array_A": {
                "include_in_parent": "true",
                "properties": {
                    "a": {
                        "type": "integer"
                    },
                    "b": {
                        "index": "not_analyzed",
                        "type": "string"
                    }
                },
                "type": "nested"
            },
            "array_B": {
                "properties": {
                    "a": {
                        "index": "not_analyzed",
                        "type": "string"
                    },
                    "b": {
                        "type": "integer"
                    }
                },
                "type": "nested"
            },
            "A": {
                "index": "not_analyzed",
                "type": "string"
            },
            "B": {
                "index": "not_analyzed",
                "type": "string"
            }      
        }
    }
}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 11:48pm UTC](https://discuss.elastic.co/t/write-a-query-to-aggregate-by-any-fields-in-mapping/29964/2 "2017-07-05T23:48:07Z")

</div>