Nested aggregation in nested aggregation query

Question

Having the below (abbreviated) document in Elastic Search 7.1. Focusing on questions.influencerReponse.selectAllThatApplyResponses path.

{
  "questions": [
    {
      "questionId": "79cfc6e7-731e-4d83-9dd6-82f4f39fff03",
      "questionKind": "select_all_that_apply",
      "questionText": "Have you heard of any of the following charities?",
      "questionOptions": {
        "1": "Plan International",
        "2": "Young Women's Trust",
        "3": "Women For Refugee Women",
        "4": "The FPA"
      },
      "influencerReponse": {
        "questionId": "79cfc6e7-731e-4d83-9dd6-82f4f39fff03",
        "questionKind": "select_all_that_apply",
        "text": null,
        "questionOrder": 3,
        "order": null,
        "shortAnswerResponse": null,
        "viewerSentimentResponse": null,
        "yesNoResponse": null,
        "selectAllThatApplyResponses": [
          {
            "key": "2",
            "value": "Young Women's Trust"
          }
        ]
      }
    }
  ]
}

I want to get the term aggregations for the key or the value, both are keyword type. I accomplished that before but not in the level of selectAllThatApplyResponses nested type.

Here's what I have so far and throwing the below error.

{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "sponsorshipId": {
                            "value": "33c7140f-23ae-46f2-a0fe-49e2251114e4"
                        }
                    }
                }
            ]
        }
    },
    "track_total_hits": true,
    "size": 0,
    "aggs": {
        "select_all_that_apply_responses": {
            "nested": {
                "path": "questions"
            },
            "aggs": {
                "filter_types": {
                    "filter": {
                        "bool": {
                            "must": [
                                {
                                    "match": {
                                        "questions.questionId": "79cfc6e7-731e-4d83-9dd6-82f4f39fff03"
                                    }
                                }
                            ]
                        }
                    },
                    "aggs": {
                        "select_all_that_apply_nested": {
                            "nested": {
                                "path": "questions.influencerReponse.selectAllThatApplyResponses"
                            },
                            "aggs": {
                                "terms": {
                                    "field": "questions.influencerReponse.selectAllThatApplyResponses.key"
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

I am receiving the below error.

{
    "error": {
        "root_cause": [
            {
                "type": "parsing_exception",
                "reason": "Expected [START_OBJECT] under [field], but got a [VALUE_STRING] in [terms]",
                "line": 42,
                "col": 46
            }
        ],
        "type": "parsing_exception",
        "reason": "Expected [START_OBJECT] under [field], but got a [VALUE_STRING] in [terms]",
        "line": 42,
        "col": 46
    },
    "status": 400
}

Jozef - Spatialized.io · Accepted Answer · 2023-10-20 09:58:47Z

1

The final terms agg needs a name too -- I called it select_all_that_apply_nested_terms .

...

  "select_all_that_apply_nested":{
    "nested":{
      "path":"questions.influencerReponse.selectAllThatApplyResponses"
    },
    "aggs":{
      "select_all_that_apply_nested_terms":{
        "terms":{
          "field":"questions.influencerReponse.selectAllThatApplyResponses.key"
        }
      }
    }
  }

...

edited Oct 20, 2023 at 9:58

answered Mar 29, 2020 at 22:33

Jozef - Spatialized.io

17k4 gold badges29 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

xplat Over a year ago

I just gave me a facepalm...this JSON nesting... thanks for the resolutions :)

Jozef - Spatialized.io Over a year ago

Yup. Been there a few times myself ;)

xplat Over a year ago

Do you have an idea of how to distinct the terms results of select_all_that_apply_nested_terms? I know I can use cardinality but I need to set the field to a parent field.

Jozef - Spatialized.io Over a year ago

Yea I'm still unclear as to what the best approach in such a case is but I've battle-tested scripted metric aggs for unique counts: stackoverflow.com/a/60617173/8160318 towards the end of the answer

xplat Over a year ago

Sounds like you needed a sum of the doc_count of the aggregations results. No it's not the same, I am just distinct the documents count based on a field so it will give me the unique documents for this field.

|

Collectives™ on Stack Overflow

Nested aggregation in nested aggregation query

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related