Elastic group query for multiple fileds

[veerabhadraiah]

Hi Team,

Help me to convert mysql query to ElasticSearch query.

select id, eventIdx, sitename,machine, nid, nocstatus, reportDate,priority from (select id, eventIdx, sitename,machine, nid, nocstatus, reportDate,priority from event.tempGraphSummary where reportDate between '2018-04-04' and '2018-04-19' and priority=1 and sitename = 'notifyconsole__2018000743' order by id desc) as t group by sitename,machine,priority,reportDate,nid;

which gives me result

To achieve same result i tried

{
	"size": 0,
	"query": {
		"bool": {
			"must": [
				{"match": {"priority": "1"}},
				{"match": {"sitename": "notifyconsole__2018000743"}}
			],
			"filter": [ { "range": { "reportDate": { "gte": "2018-04-04", "lte" : "2018-04-19" }}} ]
		}
	},"aggs": {
		"id1_count": {
		  "terms": { "field": "sitename"},
		  "aggs": {
			"id2_count": {
			  "terms": { "field": "machine"},
			"aggs": {
			"id3_count": {
			  "terms": { "field": "priority"},
			  "aggs": {
			"id4_count": {
			  "terms": { "field": "reportDate" },
			  "aggs": {
			"id5_count": {
			  "terms": { "field": "nid"}
			  
				 }  
			   }
			  
				 }  
			   }
			  
				 }  
			   }
				 }  
			   }
			}  
		  }
		}
	 }
}

Result :-

{
	"took": 4,
	"timed_out": false,
	"_shards": {
		"total": 5,
		"successful": 5,
		"skipped": 0,
		"failed": 0
	},
	"hits": {
		"total": 8,
		"max_score": 0.0,
		"hits": []
	},
	"aggregations": {
		"id1_count": {
			"doc_count_error_upper_bound": 0,
			"sum_other_doc_count": 0,
			"buckets": [{
				"key": "notifyconsole__2018000743",
				"doc_count": 8,
				"id2_count": {
					"doc_count_error_upper_bound": 0,
					"sum_other_doc_count": 0,
					"buckets": [{
							"key": "HFND100004",
							"doc_count": 4,
							"id3_count": {
								"doc_count_error_upper_bound": 0,
								"sum_other_doc_count": 0,
								"buckets": [{
									"key": 1,
									"doc_count": 4,
									"id4_count": {
										"doc_count_error_upper_bound": 0,
										"sum_other_doc_count": 0,
										"buckets": [{
											"key": "2018-04-19",
											"doc_count": 4,
											"id5_count": {
												"doc_count_error_upper_bound": 0,
												"sum_other_doc_count": 0,
												"buckets": [{
													"key": 1364,
													"doc_count": 4
												}]
											}
										}]
									}
								}]
							}
						},
						{
							"key": "HFND100015",
							"doc_count": 4,
							"id3_count": {
								"doc_count_error_upper_bound": 0,
								"sum_other_doc_count": 0,
								"buckets": [{
									"key": 1,
									"doc_count": 4,
									"id4_count": {
										"doc_count_error_upper_bound": 0,
										"sum_other_doc_count": 0,
										"buckets": [{
											"key": "2018-04-19",
											"doc_count": 4,
											"id5_count": {
												"doc_count_error_upper_bound": 0,
												"sum_other_doc_count": 0,
												"buckets": [{
													"key": 1364,
													"doc_count": 4
												}]
											}
										}]
									}
								}]
							}
						}
					]
				}
			}]
		}
	}
}

But gives me different result.

Thanks,
Veera