Aggregations over all nested objects in a key in ElasticSearch

Question

I have a document in the index 'submissions' which looks something like this,

{
  "took" : 18,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "submissions",
        "_type" : "_doc",
        "_id" : "90_169",
        "_score" : 1.0,
        "_source" : {
          "id" : "90_169",
          "locked" : false,
          "account_id" : 5,
          "campaign_id" : 90,
          "contact_id" : 1179,
          "submission_id" : 169,
          "answers" : [
            {
              "question_id" : 8451,
              "answer_bool" : true
            },
            {
              "question_id" : 8452,
              "answer_bool" : false
            },
            {
              "question_id" : 8453,
              "answer_bool" : true
            },
            {
              "question_id" : 8454,
              "answer_bool" : false
            }
          ]
        }
      }
    ]
  }
}

This is just one document.

I want to aggregate over all the questions to get a final bucket aggregations which shows number of true and false for each question_id.

Any insights on how to achieve this ?

what do you mean by number of true and false. Can you please share your expected search result as well ? — Bhavya
– Bhavya, Commented Nov 23, 2020 at 15:43
What I meant was to get a result showing each question_id with count of both possible values true or false, eg: question_id: 8454, true_count: 5, false_count: 3. Something like this.. The example i meant can be made in this format after restructuring in JS but the idea is to get count of true and count if false for each question id. @Bhavya — VPR
– VPR, Commented Nov 23, 2020 at 15:51
so considering the example document you have given in the question, for question_id:8454, will it be like true_count: 0, false_count: 2. ? — Bhavya
– Bhavya, Commented Nov 23, 2020 at 15:53
No, the single doc contains different question ids if u look carefully. Each doc will have same structure. — VPR
– VPR, Commented Nov 23, 2020 at 15:54
Consider having 3-4 documents similar to the above, ignore the skipped key. Doc 1, question 8454 is answer_bool true, say doc 2 same question is again true, doc 3, same question its false... now the same for all question ids in every doc, so.. question 8454 true_count 2, false count is 1,... question 8453... — VPR
– VPR, Commented Nov 23, 2020 at 16:00

Bhavya · Accepted Answer · 2020-11-23 16:25:08Z

You need to use nested aggregation along with terms and filter aggregation

Adding a working example with index mapping, data, search query, and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "answers": {
        "type": "nested"
      }
    }
  }
}

Index data:

{
  "id": "90_169",
  "locked": false,
  "account_id": 5,
  "campaign_id": 90,
  "contact_id": 1179,
  "submission_id": 169,
  "answers": [
    {
      "question_id": 8451,
      "answer_bool": true
    },
    {
      "question_id": 8452,
      "answer_bool": false
    },
    {
      "question_id": 8453,
      "answer_bool": true
    },
    {
      "question_id": 8454,
      "answer_bool": false
    }
  ]
}
{
  "id": "90_169",
  "locked": false,
  "account_id": 5,
  "campaign_id": 90,
  "contact_id": 1179,
  "submission_id": 169,
  "answers": [
    {
      "question_id": 8451,
      "answer_bool": true
    },
    {
      "question_id": 8452,
      "answer_bool": false
    },
    {
      "question_id": 8453,
      "answer_bool": true
    },
    {
      "question_id": 8454,
      "answer_bool": true
    }
  ]
}
{
  "id": "90_169",
  "locked": false,
  "account_id": 5,
  "campaign_id": 90,
  "contact_id": 1179,
  "submission_id": 169,
  "answers": [
    {
      "question_id": 8451,
      "answer_bool": true
    },
    {
      "question_id": 8452,
      "answer_bool": false
    },
    {
      "question_id": 8453,
      "answer_bool": true
    },
    {
      "question_id": 8454,
      "answer_bool": true
    }
  ]
}

Search Query:

{
  "size": 0,
  "aggs": {
    "nested_Agg": {
      "nested": {
        "path": "answers"
      },
      "aggs": {
        "id": {
          "terms": {
            "field": "answers.question_id"
          },
          "aggs": {
            "true_count": {
              "filter": {
                "term": {
                  "answers.answer_bool": "true"
                }
              }
            },
            "false_count": {
              "filter": {
                "term": {
                  "answers.answer_bool": "false"
                }
              }
            }
          }
        }
      }
    }
  }
}

Search Result:

"aggregations": {
    "nested_Agg": {
      "doc_count": 12,
      "id": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": 8451,
            "doc_count": 3,
            "false_count": {
              "doc_count": 0
            },
            "true_count": {
              "doc_count": 3
            }
          },
          {
            "key": 8452,
            "doc_count": 3,
            "false_count": {
              "doc_count": 3
            },
            "true_count": {
              "doc_count": 0
            }
          },
          {
            "key": 8453,
            "doc_count": 3,
            "false_count": {
              "doc_count": 0
            },
            "true_count": {
              "doc_count": 3
            }
          },
          {
            "key": 8454,
            "doc_count": 3,
            "false_count": {
              "doc_count": 1
            },
            "true_count": {
              "doc_count": 2
            }
          }
        ]
      }
    }
  }

@VPR did you get a chance to go through my answer, looking forward to get feedback from you :)
Wow. I'm new to ES and thank you very much. I'm learning a lot from your answers.

Collectives™ on Stack Overflow

Aggregations over all nested objects in a key in ElasticSearch

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related