# Need the query to get the only matched records **URL:** \ **Category:** Elasticsearch\ **Created:** [March 20, 2024, 8:37am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793 "2024-03-20T08:37:56Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![IndRajesh](https://avatars.discourse-cdn.com/v4/letter/i/439d5e/32.png) [@IndRajesh](https://discuss.elastic.co/u/IndRajesh)\ **Post date:** [March 20, 2024, 8:37am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/1 "2024-03-20T08:37:56Z")
HI , I need to get the only matched records from table:employee,users for the below mapping. put /testsqljoin { "mappings" : { "properties" : { ``` "first_name" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "table" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "userid" : { "type" : "nested", "properties" : { "user_id" : { "type" : "integer" } } } } } ``` } PUT /testsqljoin/\_doc/1 { "userid": { "user\_id": [123, 456,910] }, "email": "[jane.doe@example.com](mailto:jane.doe@example.com)", "first\_name": "sachin", "table": "employee" } PUT /testsqljoin/\_doc/2 { "userid": { "user\_id": [245] }, "email": "[satish@example.com](mailto:satish@example.com)", "first\_name": "satish", "table": "employee" } PUT /testsqljoin/\_doc/3 { "userid": { "user\_id": [789, 910] }, "table": "users" } Now i need Data whose userid :910 matches in employee,users table only. (userid :910 exists both employee,users table so i need to get the first\_name:sachin . Is there anyway ? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [March 20, 2024, 8:48am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/2 "2024-03-20T08:48:13Z")
> [@IndRajesh](#): > > "user\_id": [123, 456,910] > [@IndRajesh](#): > > ```auto > "userid" : { > "type" : "nested", > > ``` As you have a list of values and not complex JSON documents there is no point on using a nested mapping. > [@IndRajesh](#): > > Now i need Data whose userid :910 matches in employee,users table only. > (userid :910 exists both employee,users table so i need to get the first\_name:sachin . > Is there anyway ? I do not understand what you are looking to achieve. It would be helpful if you could provide an example of the query input you would like to supply and the result you are looking for. Note that Elasticsearch does not support joins between documents, so you may need to change how you are indexing data. ---
**Author:** ![IndRajesh](https://avatars.discourse-cdn.com/v4/letter/i/439d5e/32.png) [@IndRajesh](https://discuss.elastic.co/u/IndRajesh)\ **Post date:** [March 20, 2024, 8:57am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/3 "2024-03-20T08:57:34Z")
> [@Christian\_Dahlqvist](#): > > query in Hi , Please find my input as userid:910 ,then it must match with employee.userid & users.userid then give the employee.firstname. If didnt match ,no result expecting. ---
**Author:** ![IndRajesh](https://avatars.discourse-cdn.com/v4/letter/i/439d5e/32.png) [@IndRajesh](https://discuss.elastic.co/u/IndRajesh)\ **Post date:** [March 20, 2024, 9:00am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/4 "2024-03-20T09:00:22Z")
Please find my input as userid:910 ,then it must match with employee.userid & users.userid then give the employee.firstname. sample example : userid 910 exists in employee & users [userid],then i need to get employee\_firstname : sachin as output. if userid 123 then no result (expecting) beacuse 123 not exists in USERS table if userid 789 also then no result(expecting) ---
**Author:** ![IndRajesh](https://avatars.discourse-cdn.com/v4/letter/i/439d5e/32.png) [@IndRajesh](https://discuss.elastic.co/u/IndRajesh)\ **Post date:** [March 20, 2024, 9:09am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/5 "2024-03-20T09:09:06Z")
i given 123 userid,i should not get result (beacuse 123 is not exists in USERS ),but getting same in the below query GET /testindextest2/\_search { "query": { "bool": { "filter": [ { "nested": { "path": "userid", "query": { "terms": { "userid.user\_id": [123] } } } }, { "bool": { "should": [ { "term": { "table.keyword": "employee" } }, { "term": { "table.keyword": "users" } } ] } } ] } } } ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [March 20, 2024, 9:10am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/6 "2024-03-20T09:10:02Z")
You can do that by only querying document `1` as this contains all the data required. What is the point of considering document `3`? As explained earlier Elasticsearch does not support joins, so if you want to consider data in both documents you need to denormalise your data. What you seem to want to do is not possible in Elasticsearch. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 17, 2024, 9:10am UTC](https://discuss.elastic.co/t/need-the-query-to-get-the-only-matched-records/355793/7 "2024-04-17T09:10:29Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.