Need the query to get the only matched records

Elastic Stack Elasticsearch
1

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",
"first_name": "sachin",
"table": "employee"
}

PUT /testsqljoin/_doc/2
{
"userid": {
"user_id": [245]
},
"email": "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 ?

2

As you have a list of values and not complex JSON documents there is no point on using a nested mapping.

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.

3

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.

4

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)

5

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" } }
]
}
}
]
}
}
}

6

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.

7

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