Elastic Search Query on Two Documents

Elastic Stack Elasticsearch
1

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

2

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

3

Need Help on the above query.

4

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.

5

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

6

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

7

Yes, but can you help me here for query?

8

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.

9

@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.

10

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

11

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