How can we convert join query into elastic search

Elastic Stack Elasticsearch
#1

Dear All.

i having problem with below query :-1:

Please help me to convert into Elastic-search query:-

{
  "query":"SELECT * FROM dbdataindex_join_allcolumns_qa INNER JOIN  dbdataindex_join_allcolumns_qa_added_beneficiary_status ON dbdataindex_join_allcolumns_qa_added_beneficiary_status.beni_hrk= dbdataindex_join_allcolumns_qa.patient_to_care_hrk" 
}

Thanks

#2

You can not do joins with elasticsearch.

#3

Hi @dadoonet

Thanks

but i am looking to convert same query as nested/parent_child ?

because someone has given idea about that for implementation of join using nested or parent_child but i don't have idea how can i implement with same.

Thanks

#4

What is your current mapping? Are you using nested or parent/child?

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

#5

Hi @dadoonet.

Thanks for quickly replied

Actually i am writing Query like below :wink: and converting all sql query in DSL using translate(sql)

}
{ "query":"SELECT * FROM dbdataindex_join_allcolumns_qa INNER JOIN dbdataindex_join_allcolumns_qa_added_beneficiary_status ON dbdataindex_join_allcolumns_qa_added_beneficiary_status.beni_hrk= dbdataindex_join_allcolumns_qa.patient_to_care_hrk" }/>

but when try to above query using same its show :-1:

{
"error": {
    "root_cause": [
        {
            "type": "parsing_exception",
            "reason": "line 1:47: Queries with JOIN are not yet supported"
        }
    ],
    "type": "parsing_exception",
    "reason": "line 1:47: Queries with JOIN are not yet supported"
},
"status": 400

but i want same query in DSL for more info i am not writing DSL Query in any case.

by sql query just converting it into DSL using TRANSLATE and passing with team.

Thanks

#6

As the error message says, joins are not supported.

#7

Hi. @dadoonet

Thanks

but i am looking for alternatives solution by using DSL Query.

we know very well its doesn't support Joins But is there any way of implement Join.

so we can work with current request.

Note:- i want DSL Query like below(but this is not join).

< { "size" : 1000, "query" : { "bool" : { "must" : [ { "bool" : { "must" : [ { "bool" : { "must" : [ { "terms" : { "relation.keyword" : [ "P", "S" ], "boost" : 1.0 } }, { "term" : { "user_id" : { "value" : 1016, "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, { "term" : { "careprog_id" : { "value" : 7, "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, { "term" : { "active.keyword" : { "value" : "Y", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "firstname", "lastname", "sex", "active", "relation" ], "excludes" : [ ] }, "docvalue_fields" : [ { "field" : "dob", "format" : "epoch_millis" }, { "field" : "user_patient_hrk" }, { "field" : "user_id" }, { "field" : "careprog_id" } ], "sort" : [ { "_doc" : { "order" : "asc" } } ] } />

#8

Yes but this depends on your mapping as I asked:

#9

Hi @dadoonet .

Thanks

but i don't have idea about implementation of parent_child and nested query.

Note :- i have one more complex issue please review and please try to help?

Thanks

#10

Here are some links you could look at:

https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html
https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html

HTH

#11

Hi @dadoonet.

Thanks

but looking for like below use case i think this is very simple for you to resolve my use case issue.

Thanks
HadoopHelp

#12

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