# Is it possible to run dynamic sql query in elastic search

**URL:** <https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276>\
**Category:** Elasticsearch\
**Created:** [September 5, 2019, 2:51pm UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276 "2019-09-05T14:51:32Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [September 5, 2019, 2:51pm UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/1 "2019-09-05T14:51:32Z")

</div>

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

---

<div class="post-metadata">

**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:** [September 7, 2019, 10:46am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/2 "2019-09-07T10:46:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [September 9, 2019, 7:06am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/3 "2019-09-09T07:06:35Z")

</div>

Hi @Christian_Dahlqvist.

Thanks

my use is below :-

please check It and please try to help.

> [@Elastic-search advanced search query issue](https://discuss.elastic.co/t/elastic-search-advanced-search-query-issue/198254/3):
>
> Hi @dadoonet. Thanks i am trying to search the records based on below sql query condition -1 condition :- 01 i have 8-fields in current index use case and i want to search all fields value based on provided value with fields like- field1:'data' field2:'sata' field5:2424 field8:PHM Condition02:- in above search field i am not providing field value for field3,4,6,7 but we need this field for next search in current query and want exact matching result with previous - field1:'data' f…

Thanks  
HadoopHelp

---

<div class="post-metadata">

**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:** [September 9, 2019, 7:10am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/4 "2019-09-09T07:10:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [September 9, 2019, 7:21am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/5 "2019-09-09T07:21:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [September 9, 2019, 7:48am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/6 "2019-09-09T07:48:49Z")

</div>

Hi @Christian_Dahlqvist

reply on this if got this question please 🙏

---

<div class="post-metadata">

**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:** [September 9, 2019, 8:07am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/7 "2019-09-09T08:07:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [September 9, 2019, 9:07am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/8 "2019-09-09T09:07:43Z")

</div>

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.

---

<div class="post-metadata">

**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:** [September 9, 2019, 9:20am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/9 "2019-09-09T09:20:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [September 9, 2019, 9:40am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/10 "2019-09-09T09:40:30Z")

</div>

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 🙏

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 7, 2019, 9:40am UTC](https://discuss.elastic.co/t/is-it-possible-to-run-dynamic-sql-query-in-elastic-search/198276/11 "2019-10-07T09:40:37Z")

</div>

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