Elastic Search Aggregation and Complex Query

Elastic Stack Elasticsearch
1

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.

2

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.

3

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