# Elastic Search Aggregation and Complex Query

**URL:** <https://discuss.elastic.co/t/elastic-search-aggregation-and-complex-query/238706>\
**Category:** Elasticsearch\
**Created:** [June 25, 2020, 3:13pm UTC](https://discuss.elastic.co/t/elastic-search-aggregation-and-complex-query/238706 "2020-06-25T15:13:20Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![surajdalvi](https://avatars.discourse-cdn.com/v4/letter/s/85f322/32.png) [@surajdalvi](https://discuss.elastic.co/u/surajdalvi)\
**Post date:** [June 25, 2020, 3:13pm UTC](https://discuss.elastic.co/t/elastic-search-aggregation-and-complex-query/238706/1 "2020-06-25T15:13:21Z")

</div>

I have created the index

```
PUT ten2
{
	"mappings": {
		"documents": {
			"properties": {
				"title": {
					"type": "text",
					"fields": {
						"raw": {
							"type": "keyword"
						}
					}
				},"uid": {
					"type": "text",
					"fields": {
						"raw": {
							"type": "keyword"
						}
					}
				},
				"publish_details": {
					"type": "nested",
					"properties": {
						"environment": {
							"type": "keyword"
						},
						"locale": {
							"type": "keyword"
						},
						"time": {
							"type": "date"
						},
						"version": {
							"type": "integer"
						}
					}
				}
			}
		}
	}
}

```

and added documents into it. here is the list of documents:

```
   [{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt69b62b48bbed1fb6_en-us",
	"_source": {
		"publish_details": [{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:11:25.276Z",
				"locale": "en-us",
				"user": "bltaadab2f531206e9d",
				"version": 1
			},
			{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:11:25.276Z",
				"locale": "hi-in",
				"user": "bltaadab2f531206e9d",
				"version": 1
			}
		],
		"title": "Entry 1",
		"uid": "blt69b62b48bbed1fb6"
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt69b62b48bbed1fb6_mr-in",
	"_source": {
		"publish_details": [{
			"environment": "blt603fe91adbdcff66",
			"time": "2020-06-24T12:12:35.467Z",
			"locale": "mr-in",
			"user": "bltaadab2f531206e9d",
			"version": 1
		}],
		"title": "Entry 3",
		"uid": "blt69b62b48bbed1fb6"
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt4044c5198122a3ed_en-us",
	"_source": {
		"publish_details": [{
			"environment": "blt603fe91adbdcff66",
			"time": "2020-06-24T12:10:46.430Z",
			"locale": "en-us",
			"user": "bltaadab2f531206e9d",
			"version": 1
		},{
			"environment": "blt603fe91adbdcff6690",
			"time": "2020-06-24T12:10:46.430Z",
			"locale": "en-us",
			"user": "bltaadab2f531206e9d",
			"version": 1
		}],
		"title": "Entry 10",
		"uid": "blt4044c5198122a3ed"
	}
}

```

]  
and I want the following result

```
    [
 {
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt4044c5198122a3ed_en-us",
	"_source": {
		"publish_details": [{
			"environment": "blt603fe91adbdcff66",
			"time": "2020-06-24T12:10:46.430Z",
			"locale": "en-us",
			"user": "bltaadab2f531206e9d",
			"version": 1
		},{
			"environment": "blt603fe91adbdcff6690",
			"time": "2020-06-24T12:10:46.430Z",
			"locale": "en-us",
			"user": "bltaadab2f531206e9d",
			"version": 1
		}],
		"title": "Entry 10",
		"uid": "blt4044c5198122a3ed"
	}
}

]

```

I am using the following query to get the result

```
GET ten2/_search
{
	"query": {
		"bool": {
			"must": [{
				"bool": {
					"must_not": [{
						"bool": {
							"must": [{
								"nested": {
									"path": "publish_details",
									"query": {
										"term": {
										"publish_details.environment": "blt603fe91adbdcff66"
										}
									}
								}
							}, {
								"nested": {
									"path": "publish_details",
									"query": {
										"term": {
											"publish_details.locale": "en-us"
										}
									}
								}
							}, {
								"nested": {
									"path": "publish_details",
									"query": {
										"term": {
											"publish_details.locale": "hi-in"
										}
									}
								}
							}, {
								"nested": {
									"path": "publish_details",
									"query": {
										"term": {
											"publish_details.locale": "mr-in"
										}
									}
								}
							}]
						}
					}]
				}
			}
		}
	}
}

```

kindly help me with a query to get the expected result. The first two documents having the same uid only publish\_details.locale is different. I am using must query within must\_not to get the result, currently I am getting all three documents but I want only the last one. I have a million documents.

---

<div class="post-metadata">

**Author:** ![surajdalvi](https://avatars.discourse-cdn.com/v4/letter/s/85f322/32.png) [@surajdalvi](https://discuss.elastic.co/u/surajdalvi)\
**Post date:** [June 25, 2020, 3:14pm UTC](https://discuss.elastic.co/t/elastic-search-aggregation-and-complex-query/238706/2 "2020-06-25T15:14:32Z")

</div>

uid of the first two documents are the same that means uid: blt69b62b48bbed1fb6 is published on all 3 locales. so I don't want that document in the result.

---

<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:** [July 23, 2020, 3:14pm UTC](https://discuss.elastic.co/t/elastic-search-aggregation-and-complex-query/238706/3 "2020-07-23T15:14:36Z")

</div>

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