How to query an elastic search to get two dimensional table as output?
Example:
Students as columns, Subjects as rows and marks as values in Student Subject table.
Aggregations provide the ability to summarise data in different categories.
It is useful to think about a tree structure rather than to talk of tables. The terms aggregation can organise documents into buckets, each of which can be further subdivided by other aggregations into further sub-buckets. Your query would look something like this:
POST test/result
{
"student":"albert",
"subject":"physics",
"score":99
}
POST test/result
{
"student":"benoit",
"subject":"mathematics",
"score":97
}
POST test/result
{
"student":"joe",
"subject":"mathematics",
"score":33
}
GET test/result/_search
{
"size":0,
"aggregations":{
"subjects":{
"terms":{
"field":"subject"
},
"aggregations":{
"students":{
"terms":{
"field":"student"
},
"aggregations":{
"max_score":{
"max":{
"field":"score"
}
}
}
}
}
}
}
}
This last query returns a tree structure like this:
{
"took": 29,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"failed": 0
},
"hits": {
"total": 3,
"max_score": 0,
"hits": []
},
"aggregations": {
"subjects": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "mathematics",
"doc_count": 2,
"students": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "benoit",
"doc_count": 1,
"max_score": {
"value": 97
}
},
{
"key": "joe",
"doc_count": 1,
"max_score": {
"value": 33
}
}
]
}
},
{
"key": "physics",
"doc_count": 1,
"students": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "albert",
"doc_count": 1,
"max_score": {
"value": 99
}
}
]
}
}
]
}
}
}