# Elastic group query for multiple fileds

**URL:** <https://discuss.elastic.co/t/elastic-group-query-for-multiple-fileds/128724>\
**Category:** Elasticsearch\
**Created:** [April 19, 2018, 2:18pm UTC](https://discuss.elastic.co/t/elastic-group-query-for-multiple-fileds/128724 "2018-04-19T14:18:38Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![veerabhadraiah](https://avatars.discourse-cdn.com/v4/letter/v/e495f1/32.png) [@veerabhadraiah](https://discuss.elastic.co/u/veerabhadraiah)\
**Post date:** [April 19, 2018, 2:18pm UTC](https://discuss.elastic.co/t/elastic-group-query-for-multiple-fileds/128724/1 "2018-04-19T14:18:38Z")

</div>

Hi Team,

Help me to convert mysql query to ElasticSearch query.

```auto
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

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/9/a9c4a9d2a25e2c9cfe75b836f02a66736c870d8d.png)

To achieve same result i tried

```auto
{
	"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 :-

```auto
{
	"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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 17, 2018, 2:18pm UTC](https://discuss.elastic.co/t/elastic-group-query-for-multiple-fileds/128724/2 "2018-05-17T14:18:53Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.