# DSL. Convert from SQL statement

**URL:** <https://discuss.elastic.co/t/dsl-convert-from-sql-statement/6193>\
**Category:** Elasticsearch\
**Created:** [December 19, 2011, 5:40pm UTC](https://discuss.elastic.co/t/dsl-convert-from-sql-statement/6193 "2011-12-19T17:40:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yada](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yada/32/3042_2.png) [@Yada](https://discuss.elastic.co/u/Yada)\
**Post date:** [December 19, 2011, 5:40pm UTC](https://discuss.elastic.co/t/dsl-convert-from-sql-statement/6193/1 "2011-12-19T17:40:36Z")

</div>

Hi folks,

I'm new to ES. I don't think I fully understand the concept of query  
and filters. In my case I just want to use filters as I am using ES  
to replace mysql.

How would I convert the following SQL statement into elasticsearch  
query?

SELECT \* FROM advertiser  
WHERE company like '%com%'  
AND sales\_rep IN (1,2)

What I have so far:

curl -XGET 'localhost:9200/advertisers/advertiser/\_search?pretty=true'  
-d '  
{  
"query" : {  
"bool" : {  
"must" : {  
"wildcard" : { "company" : "_test_" }  
}  
}  
},  
"size":1000000  
}'

Thanks

---

<div class="post-metadata">

**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\
**Post date:** [December 19, 2011, 10:00pm UTC](https://discuss.elastic.co/t/dsl-convert-from-sql-statement/6193/2 "2011-12-19T22:00:04Z")

</div>

Hi Yada,  
First off, you really want to understand that products like  
elasticsearch search against term based inverted indexes where each  
term has the set of documents that contain it. The way that these  
terms are created from the submitted text is the called the analysis  
process and is critical to ensure the search is behaving as you wish.

While the example you posted above using wildcards will work, it won't  
work efficiently. Wildcards and especially leading wildcards should be  
avoided for decent performance. Instead, you should consider using an  
analyzer with ngrams ([Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/)  
index-modules/analysis/ngram-tokenfilter.html) applied to break up  
words into various substrings.

So, the string "test" could get broken up like this:  
te  
es  
st  
tes  
est  
test

and searched on without using wildcards.

Hope this helps.

Paul

On Dec 19, 10:40 am, Yada [yada.k...@gmail.com](mailto:yada.k...@gmail.com) wrote:

> Hi folks,
> 
> I'm new to ES. I don't think I fully understand the concept of query  
> and filters. In my case I just want to use filters as I am using ES  
> to replace mysql.
> 
> How would I convert the following SQL statement into elasticsearch  
> query?
> 
> SELECT \* FROM advertiser  
> WHERE company like '%com%'  
> AND sales\_rep IN (1,2)
> 
> What I have so far:
> 
> curl -XGET 'localhost:9200/advertisers/advertiser/\_search?pretty=true'  
> -d '  
> {  
> "query" : {  
> "bool" : {  
> "must" : {  
> "wildcard" : { "company" : "_test_" }  
> }  
> }  
> },  
> "size":1000000
> 
> }'
> 
> Thanks

---

<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:** [July 6, 2017, 3:44am UTC](https://discuss.elastic.co/t/dsl-convert-from-sql-statement/6193/3 "2017-07-06T03:44:56Z")

</div>