Multi-Dimensionality

Elastic Stack Elasticsearch
1

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.

2

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
						}
					 }
				  ]
			   }
			}
		 ]
	  }
   }
}