Elastic Search Query on Two Documents

Hi,
I have created the index

PUT ten2
{
	"mappings": {
		"documents": {
			"properties": {
				"title": {
					"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",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"_locales": [
			"mr-in",
			"hi-in",
			"en-us"
		],
		"org_uid": "blt6c838226ba29f79d",
		"publish_details": [{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:11:25.276Z",
				"locale": "mr-in",
				"user": "bltaadab2f531206e9d",
				"version": 1
			},
			{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:11:25.276Z",
				"locale": "hi-in",
				"user": "bltaadab2f531206e9d",
				"version": 1
			}
		],
		"created_at": "2020-06-24T12:11:06.946Z",
		"_in_progress": false,
		"title": "Entry 2",
		"locale": "en-us",
		"created_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"version": 1,
		"url": "/entry-2",
		"tags": [],
		"uid": "blt69b62b48bbed1fb6",
		"updated_at": "2020-06-24T12:11:06.946Z",
		"api_key": "blt9a73af140d639159",
		"updated_by": "bltaadab2f531206e9d",
		"fields": []
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt24be5dfc069141ef_hi-in",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"_locales": [
			"mr-in",
			"hi-in"
		],
		"org_uid": "blt6c838226ba29f79d",
		"publish_details": [{
			"environment": "blt603fe91adbdcff66",
			"time": "2020-06-24T12:12:35.467Z",
			"locale": "hi-in",
			"user": "bltaadab2f531206e9d",
			"version": 1
		}],
		"created_at": "2020-06-24T12:12:19.814Z",
		"_in_progress": false,
		"title": "Entry 3 hindi",
		"locale": "hi-in",
		"created_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"version": 1,
		"url": "/entry-3",
		"tags": [],
		"uid": "blt24be5dfc069141ef",
		"updated_at": "2020-06-24T12:12:19.814Z",
		"api_key": "blt9a73af140d639159",
		"updated_by": "bltaadab2f531206e9d",
		"fields": []
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt4044c5198122a3ed_en-us",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"_locales": [
			"mr-in",
			"hi-in",
			"en-us"
		],
		"org_uid": "blt6c838226ba29f79d",
		"publish_details": [{
			"environment": "blt603fe91adbdcff66",
			"time": "2020-06-24T12:10:46.430Z",
			"locale": "en-us",
			"user": "bltaadab2f531206e9d",
			"version": 1
		}],
		"created_at": "2020-06-24T12:10:31.236Z",
		"_in_progress": false,
		"title": "Entry 1",
		"locale": "en-us",
		"created_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"version": 1,
		"url": "/entry-1",
		"tags": [],
		"uid": "blt4044c5198122a3ed",
		"updated_at": "2020-06-24T12:10:31.236Z",
		"api_key": "blt9a73af140d639159",
		"updated_by": "bltaadab2f531206e9d",
		"fields": []
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt253222b2edb34318_mr-in",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"_locales": [
			"mr-in"
		],
		"org_uid": "blt6c838226ba29f79d",
		"publish_details": [{
			"environment": "blt603fe91adbdcff66",
			"time": "2020-06-24T12:14:13.838Z",
			"locale": "mr-in",
			"user": "bltaadab2f531206e9d",
			"version": 1
		}],
		"created_at": "2020-06-24T12:13:58.530Z",
		"_in_progress": false,
		"title": "Entry 5 marthi",
		"locale": "mr-in",
		"created_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"version": 1,
		"url": "/entry-5",
		"tags": [],
		"uid": "blt253222b2edb34318",
		"updated_at": "2020-06-24T12:13:58.530Z",
		"api_key": "blt9a73af140d639159",
		"updated_by": "bltaadab2f531206e9d",
		"fields": []
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt96fdc195b616c6e2_en-us",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"title": "Entry 4",
		"uid": "blt96fdc195b616c6e2",
		"locale": "en-us",
		"_locales": [
			"mr-in",
			"hi-in",
			"en-us"
		],
		"tags": [],
		"created_at": "2020-06-24T12:12:57.355Z",
		"updated_at": "2020-06-24T12:12:57.355Z",
		"created_by": "bltaadab2f531206e9d",
		"updated_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"api_key": "blt9a73af140d639159",
		"version": 1,
		"org_uid": "blt6c838226ba29f79d",
		"_in_progress": false,
		"url": "/entry-4",
		"fields": [],
		"publish_details": []
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt253222b2edb34318_en-us",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"_locales": [
			"hi-in",
			"en-us"
		],
		"org_uid": "blt6c838226ba29f79d",
		"publish_details": [{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:13:36.108Z",
				"locale": "hi-in",
				"user": "bltaadab2f531206e9d",
				"version": 1
			},
			{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:13:36.108Z",
				"locale": "en-us",
				"user": "bltaadab2f531206e9d",
				"version": 1
			}
		],
		"created_at": "2020-06-24T12:13:16.669Z",
		"_in_progress": false,
		"title": "Entry 5",
		"locale": "en-us",
		"created_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"version": 1,
		"url": "/entry-5",
		"tags": [],
		"uid": "blt253222b2edb34318",
		"updated_at": "2020-06-24T12:13:16.669Z",
		"api_key": "blt9a73af140d639159",
		"updated_by": "bltaadab2f531206e9d",
		"fields": []
	}
},
{
	"_index": "ten2",
	"_type": "documents",
	"_id": "blt24be5dfc069141ef_en-us",
	"_score": 1.0,
	"_routing": "blt6c838226ba29f79d",
	"_source": {
		"title": "Entry 3",
		"uid": "blt24be5dfc069141ef",
		"locale": "en-us",
		"_locales": [
			"en-us"
		],
		"tags": [],
		"created_at": "2020-06-24T12:12:05.079Z",
		"updated_at": "2020-06-24T12:12:05.079Z",
		"created_by": "bltaadab2f531206e9d",
		"updated_by": "bltaadab2f531206e9d",
		"_content_type_uid": "s5",
		"api_key": "blt9a73af140d639159",
		"version": 1,
		"org_uid": "blt6c838226ba29f79d",
		"_in_progress": false,
		"url": "/entry-3",
		"fields": [],
		"publish_details": []
	}
}

]
and I want the following result

    [
  {
		"_index": "ten2",
		"_type": "documents",
		"_id": "blt4044c5198122a3ed_en-us",
		"_score": 1.0,
		"_routing": "blt6c838226ba29f79d",
		"_source": {
			"_locales": [
				"mr-in",
				"hi-in",
				"en-us"
			],
			"org_uid": "blt6c838226ba29f79d",
			"publish_details": [{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:10:46.430Z",
				"locale": "en-us",
				"user": "bltaadab2f531206e9d",
				"version": 1
			}],
			"created_at": "2020-06-24T12:10:31.236Z",
			"_in_progress": false,
			"title": "Entry 1",
			"locale": "en-us",
			"created_by": "bltaadab2f531206e9d",
			"_content_type_uid": "s5",
			"version": 1,
			"url": "/entry-1",
			"tags": [],
			"uid": "blt4044c5198122a3ed",
			"updated_at": "2020-06-24T12:10:31.236Z",
			"api_key": "blt9a73af140d639159",
			"updated_by": "bltaadab2f531206e9d",
			"fields": []
		}
	},
	{
		"_index": "ten2",
		"_type": "documents",
		"_id": "blt69b62b48bbed1fb6_en-us",
		"_score": 1.0,
		"_routing": "blt6c838226ba29f79d",
		"_source": {
			"_locales": [
				"mr-in",
				"hi-in",
				"en-us"
			],
			"org_uid": "blt6c838226ba29f79d",
			"publish_details": [{
					"environment": "blt603fe91adbdcff66",
					"time": "2020-06-24T12:11:25.276Z",
					"locale": "mr-in",
					"user": "bltaadab2f531206e9d",
					"version": 1
				},
				{
					"environment": "blt603fe91adbdcff66",
					"time": "2020-06-24T12:11:25.276Z",
					"locale": "hi-in",
					"user": "bltaadab2f531206e9d",
					"version": 1
				}
			],
			"created_at": "2020-06-24T12:11:06.946Z",
			"_in_progress": false,
			"title": "Entry 2",
			"locale": "en-us",
			"created_by": "bltaadab2f531206e9d",
			"_content_type_uid": "s5",
			"version": 1,
			"url": "/entry-2",
			"tags": [],
			"uid": "blt69b62b48bbed1fb6",
			"updated_at": "2020-06-24T12:11:06.946Z",
			"api_key": "blt9a73af140d639159",
			"updated_by": "bltaadab2f531206e9d",
			"fields": []
		}
	},
	{
		"_index": "ten2",
		"_type": "documents",
		"_id": "blt24be5dfc069141ef_en-us",
		"_score": 1.0,
		"_routing": "blt6c838226ba29f79d",
		"_source": {
			"title": "Entry 3",
			"uid": "blt24be5dfc069141ef",
			"locale": "en-us",
			"_locales": [
				"en-us"
			],
			"tags": [],
			"created_at": "2020-06-24T12:12:05.079Z",
			"updated_at": "2020-06-24T12:12:05.079Z",
			"created_by": "bltaadab2f531206e9d",
			"updated_by": "bltaadab2f531206e9d",
			"_content_type_uid": "s5",
			"api_key": "blt9a73af140d639159",
			"version": 1,
			"org_uid": "blt6c838226ba29f79d",
			"_in_progress": false,
			"url": "/entry-3",
			"fields": [],
			"publish_details": []
		}
	},
	{
		"_index": "ten2",
		"_type": "documents",
		"_id": "blt24be5dfc069141ef_hi-in",
		"_score": 1.0,
		"_routing": "blt6c838226ba29f79d",
		"_source": {
			"_locales": [
				"mr-in",
				"hi-in"
			],
			"org_uid": "blt6c838226ba29f79d",
			"publish_details": [{
				"environment": "blt603fe91adbdcff66",
				"time": "2020-06-24T12:12:35.467Z",
				"locale": "hi-in",
				"user": "bltaadab2f531206e9d",
				"version": 1
			}],
			"created_at": "2020-06-24T12:12:19.814Z",
			"_in_progress": false,
			"title": "Entry 3 hindi",
			"locale": "hi-in",
			"created_by": "bltaadab2f531206e9d",
			"_content_type_uid": "s5",
			"version": 1,
			"url": "/entry-3",
			"tags": [],
			"uid": "blt24be5dfc069141ef",
			"updated_at": "2020-06-24T12:12:19.814Z",
			"api_key": "blt9a73af140d639159",
			"updated_by": "bltaadab2f531206e9d",
			"fields": []
		}
	},
	{
		"_index": "ten2",
		"_type": "documents",
		"_id": "blt96fdc195b616c6e2_en-us",
		"_score": 1.0,
		"_routing": "blt6c838226ba29f79d",
		"_source": {
			"title": "Entry 4",
			"uid": "blt96fdc195b616c6e2",
			"locale": "en-us",
			"_locales": [
				"mr-in",
				"hi-in",
				"en-us"
			],
			"tags": [],
			"created_at": "2020-06-24T12:12:57.355Z",
			"updated_at": "2020-06-24T12:12:57.355Z",
			"created_by": "bltaadab2f531206e9d",
			"updated_by": "bltaadab2f531206e9d",
			"_content_type_uid": "s5",
			"api_key": "blt9a73af140d639159",
			"version": 1,
			"org_uid": "blt6c838226ba29f79d",
			"_in_progress": false,
			"url": "/entry-4",
			"fields": [],
			"publish_details": []
		}
	}
]

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"
										}
									}
								}
							}]
						}
					}]
				}
			}, {
				"terms": {
					"_content_type_uid": ["s5"]
				}
			}],
			"filter": [{
				"terms": {
					"api_key": ["blt9a73af140d639159"]
				}
			}]
		}
	}
}

kindly help me a query to get expected result

Is any aggregation query for this two combine publish_details and then query them?

Need Help on the above query.

Hi Suraj,

kindly help me a query to get expected result

Since you are seeking help I am guessing your query is not working. So you may want to express what your query is expected to do in simple english or SQL syntax.

Query is working but not getting the result that I am expecting.

If query is not returning expected results it can't be used to understand actual intent.

Yes, but can you help me here for query?

Suraj,
You have provided dataset of 7 documents and expecting to match 5. Unless you specify criteria to match these 5 documents, how can anyone help you?

As I mentioned in my first reply, if you specify your match criteria in SQL like syntax or english, someone can help you translate it to ES query.

@Vinayak_Sapre the above question link to Elastic Search Aggregation and Complex Query can you please check here for criteria.and help me there.
also, you can see https://stackoverflow.com/questions/62574006/elastic-search-aggregation-and-complex-query for more reference.

Kindly help me : https://stackoverflow.com/questions/62574006/elastic-search-aggregation-and-complex-query to get a result?

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