# Faceting/aggregations of nested fields

**URL:** <https://discuss.elastic.co/t/faceting-aggregations-of-nested-fields/308144>\
**Category:** Elasticsearch\
**Created:** [June 24, 2022, 10:19pm UTC](https://discuss.elastic.co/t/faceting-aggregations-of-nested-fields/308144 "2022-06-24T22:19:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vadim-permakoff](https://avatars.discourse-cdn.com/v4/letter/v/77aa72/32.png) [@vadim-permakoff](https://discuss.elastic.co/u/vadim-permakoff)\
**Post date:** [June 24, 2022, 10:19pm UTC](https://discuss.elastic.co/t/faceting-aggregations-of-nested-fields/308144/1 "2022-06-24T22:19:36Z")

</div>

Hi,  
I need to implement a faceting feature for nested fields. For example, I have two documents, each has an “applicability” array, which defines nested fields state, lob and year, specifying where this document is applicable:

```auto
{
  "title": "Document 1",
  "applicability": [
    {
      "state": "NY",
      "lob": "AA",
      "year": 2001
    },
    {
      "state": "NJ",
      "lob": "BB",
      "year": 2001
    },
    {
      "state": "NJ",
      "lob": "BB",
      "year": 2002
    }
  ]
}

{
  "title": "Document 2",
  "applicability": [ 
    {
      "state": "NJ",
      "lob": "AA",
      "year": 2001
    },
    {
      "state": "NJ",
      "lob": "BB",
      "year": 2001
    },
    {
      "state": "NJ",
      "lob": "BB",
      "year": 2002
    }
  ]
}

```

The mapping is very simple, all keywords with nested applicability.  
Let’s say, the task is to return all documents applicable for NJ state and display the state and lob facets.  
I run following query with aggregations:

```auto
{
    "from": 0,
    "size": 0,
    "query": {
        "nested": {
            "path": "applicability",
            "query": {
                "query_string": { "query": "applicability.state:NJ" }
            }
        }
    },
    "aggregations": {
        "applicability": {
            "nested": {
                "path": "applicability"
            },
            "aggregations": {
                "filtered": {
                    "filter": {
                        "query_string": { "query": "applicability.state:NJ" }
                    },
                    "aggregations": {
                        "state": {
                            "terms": {
                                "field": "applicability.state"
                            }
                        },
                        "lob": {
                            "terms": {
                                "field": "applicability.lob"
                            }
                        }
                    }
                }
            }
        }
    }
}

```

And I got result:

```auto
{
    "took": 9,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 2,
            "relation": "eq"
        },
        "max_score": null,
        "hits": []
    },
    "aggregations": {
        "applicability": {
            "doc_count": 6,
            "filtered": {
                "doc_count": 5,
                "state": {
                    "doc_count_error_upper_bound": 0,
                    "sum_other_doc_count": 0,
                    "buckets": [
                        {
                            "key": "NJ",
                            "doc_count": 5
                        }
                    ]
                },
                "lob": {
                    "doc_count_error_upper_bound": 0,
                    "sum_other_doc_count": 0,
                    "buckets": [
                        {
                            "key": "BB",
                            "doc_count": 4
                        },
                        {
                            "key": "AA",
                            "doc_count": 1
                        }
                    ]
                }
            }
        }
    }
}

```

The result is two documents (as expected), but faceting has state doc\_count NJ:5 and for lob doc\_count AA:1, BB:4. However only two documents are found and I expect facets for state NJ:2 and lob AA:1, BB:2. How to achieve that? Tried to use cardinality, but run into issue of approximation for more than 40000 results, tried to use scripts, it returns correct counts, but works very slow. It seems to me it should be very common problem, but I cannot find a good solution, may be I'm missing something.

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [June 26, 2022, 1:33pm UTC](https://discuss.elastic.co/t/faceting-aggregations-of-nested-fields/308144/2 "2022-06-26T13:33:31Z")

</div>

Use reverse\_nested aggregation. You can count the doc\_count of root documents.

```auto
PUT test_nested
{
  "mappings": {
    "properties": {
      "n":{
        "type":"nested",
        "properties": {
          "a":{
            "type":"keyword"
          }
        }
      }
    }
  }
}

POST test_nested/_doc
{
  "n":[
    {"a": "foo"},
    {"a":"baa"}]
}
POST test_nested/_doc
{
  "n":[
    {"a": "foo"},
    {"a": "foo"},
    {"a": "foo"},
    {"a":"baa"}]
}

GET test_nested/_search
{
  "size":0,
  "aggs":{
    "n":{
      "nested":{
        "path": "n"
      },
      "aggs":{
        "t":{
          "terms": {
            "field": "n.a",
            "size": 10
          },
          "aggs":{
            "r":{
              "reverse_nested": {}
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![vadim-permakoff](https://avatars.discourse-cdn.com/v4/letter/v/77aa72/32.png) [@vadim-permakoff](https://discuss.elastic.co/u/vadim-permakoff)\
**Post date:** [June 28, 2022, 9:11pm UTC](https://discuss.elastic.co/t/faceting-aggregations-of-nested-fields/308144/3 "2022-06-28T21:11:22Z")

</div>

The reverse\_nested aggregation works! Thank you!  
Implemented in Java as following:  
Query:  
if (grouped) {  
termsAggregationBuilder.subAggregation(AggregationBuilders.reverseNested(UNIQUE));  
}

Result:  
if (grouped) {  
ParsedReverseNested parsedReverseNested = bucket.getAggregations().get(UNIQUE);  
value = parsedReverseNested.getDocCount();  
} else {  
value = bucket.getDocCount();  
}

---

<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 26, 2022, 9:11pm UTC](https://discuss.elastic.co/t/faceting-aggregations-of-nested-fields/308144/4 "2022-07-26T21:11:31Z")

</div>

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