Elasticsearch - Grouping aggregation - 2 fields

Question

My mapping is

{
"myapp": {
    "mappings": {
        "attempts": {
            "properties": {
                "answers": {
                    "properties": {
                        "question_id": {
                            "type": "long"
                        },
                        "status": {
                            "type": "long"
                        }
                    }
                },
                "exam_id": {
                    "type": "long"
                }
            }
        }
    }
}
}

and i want to group by question_id and status

I want to know for every question_id how many has status 1 or 2

P.S. 2 attempts can have the same questions

Answer 1

First of All, you need to update your mapping and make answers a nested field. Not making it nested will make the answers to lose the correlation between question_id field and status field.

{
"myapp": {
    "mappings": {
        "attempts": {
            "properties": {
                "answers": {
                    "type":"nested", <-- Update here
                    "properties": {
                        "question_id": {
                            "type": "long"
                        },
                        "status": {
                            "type": "long"
                        }
                    }
                },
                "exam_id": {
                    "type": "long"
                }
            }
        }
    }
}
}

You can use status in a sub-aggregation as shown below

"aggs": {
    "nested_qid_agg": {
      "nested": {
        "path": "answers"
      },
      "aggs": {
        "qid": {
          "terms": {
            "field": "answers.question_id",
            "size": 0
          },
          "aggs": {
            "status": {
              "terms": {
                "field": "answers.status",
                "size": 0
              }
            }
          }
        }
      }
    }
  }