Is it possible to run dynamic sql query in elastic search

Elastic Stack Elasticsearch
1

Dear All.

i have use case in elastic search that is matching with below sql query:-

DECLARE @EmpID AS SMALLINT DECLARE @SQLQuery AS NVARCHAR(500) DECLARE @ParameterDefinition AS NVARCHAR(100) SET @EmpID = 1001 SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' SET @ParameterDefinition = '@EmpID SMALLINT' EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

so can we implement same like sql in elastic search?

Please help me for above use case?

Thanks

2

No, I do not think that can be directly translated into DSL. If you desctibe your data and what you are trying to achieve someone might be able to asist in finding a way to model and query your data.

3

Hi @Christian_Dahlqvist.

Thanks

my use is below :-

please check It and please try to help.

Thanks
HadoopHelp

4

I do not understand that example and you have not provided a recreation script so I do not think I can help with that.

5

Hi @Christian_Dahlqvist.

in simple way :-

i have index and want to all search fields in current query but condition here is :

case 01:
user can try to search all 8-fields at a time or some fields only but result should be come based on matching fields value only like : fields02:'data' and fields02:"meta"

case 02:
query should contains all 8 fields for search .

I USED :

I tried with ---should keyword--- that is fetching all matching records within index fields .
i also tried with ---must keyword--- that is fetching only passed value with fields but when we are not passing value with that fields that is not fetching any result .

Finally : i want to implement exact search match for all 8-fields but user can provide some time 2-fields ,3-fields ,4-fields .

Thanks
HadoopHelp

6

Hi @Christian_Dahlqvist

reply on this if got this question please :pray:

7

I do still not understand the problem. I suspect you will need to create the bool query based on what the user submits but Am not sure exactly what it would look like based on your description.

8

Hi @Christian_Dahlqvist.

You are correct we need to apply here bool (must/should) here but don't go with current subject description.

when i apply ---must--- it is looking for all fields value if we don't pass value in bool--must it will not fetch data.

when i apply here ---bool should then it fetching all fields data but not fetching exact matched data

but we want to fetch all exact matched data from provide fields values only .

and query contains 8-fields but user pass only 4,5 fields value then fetched matched data.

9

If the user only supplies some fields you need to construct the bool clauses accordingly. I doubt you can have a one size fits all query template if that is what you are looking for. If I have misunderstood please grooved data and sample queries as examples to explain.

10

Hi @Christian_Dahlqvist.

you are getting correct please same as you understood above :

we are looking for all in one search fields but some times user can provide some fields not complete fields but result should be come based on exact match as passing the fields values.

 one size fits all query template

Sample data:

{"_index":"dbdataindex_join_allcolumns_qa_modify_cm_28_08_2019","_type":"caremanager","_id":"W5_X3GwBthBZI3l8q","_score":1.0,"_source":{"dob":"1956-77-27T18:30:00.000Z","patientname":"Aba","city":null,"provider_type":"P","medicare":"167A","care_program_name":"C Management","@timestamp":"2019-08-29T10:00:47.614Z","dob1":"06/28/1996","log_date":"2019-77-25T18:30:00.000Z","email":null,"regionname":"M05","phone_home":"79999921","age":63,"pcp":"Lastn861 First904","active":"A","sex":"M","user_id":898888,"caremanager":"HElth","pid":249,"@version":"1","patient_provider_hrk":28888889,"careprog_id":20,"beneficiary_status":"Active"}},{"_index":"dbdataindex_join_allcolumns_qa_modify_cm_28_08_2019","_type":"caremanager","_id":"hZ_X3GwBthBZI3l8q","_score":1.0,"_source":{"dob":"1968-99-05T00:00:00.000Z","patientname":"Atu","city":null,"provider_type":"P","medicare":"1993A","care_program_name":"Di Care","@timestamp":"2019-88-29T10:00:47.636Z","dob1":"12/05/1968","log_date":null,"email":null,"regionname":"M00S","phone_home":"","age":50,"pcp":"Lastn3 Firs52","active":"A","sex":"F","user_id":29997,"caremanager":"LIANE","pid":0092,"@version":"1","patient_provider_hrk":99990,"careprog_id":1,"beneficiary_status":"Active"}},

sample query :

<{ "size" : 1000, "query" : { "bool" : { "must" : [ { "bool" : { "must" : [ { "term" : { "regionname.keyword" : { "value" : "MS5S", "boost" : 1.0 } } }, { "term" : { "caremanager.keyword" : { "value" : "HECM", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, { "term" : { "patientname.keyword" : { "value" : "Aaa", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : [ "@version", "active", "beneficiary_status", "care_program_name", "caremanager", "city", "dob1", "email", "medicare", "patientname", "pcp", "phone_home", "provider_type", "regionname", "sex" ], "excludes" : [ ] }, "docvalue_fields" : [ { "field" : "@timestamp", "format" : "epoch_millis" }, { "field" : "age" }, { "field" : "careprog_id" }, { "field" : "dob", "format" : "epoch_millis" }, { "field" : "log_date", "format" : "epoch_millis" }, { "field" : "patient_provider_hrk" }, { "field" : "pid" }, { "field" : "user_id" } ], "sort" : [ { "_doc" : { "order" : "asc" } } ] } />

above query fail if we will not pass the value for any fields and we want all fields in query for search patient records.

Thanks Very much for helping @Christian_Dahlqvist :pray:

11

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