DSL. Convert from SQL statement

Elastic Stack Elasticsearch
1

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

2

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
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 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