CODEWITHSUNDEEP
javaphpelasticsearchelasticsearch-pluginelasticsearch-net
Dixit Sourav
Dixit Sourav

Reputation: 390

Like search in Elasticsearch

I am using elasticsearch for filtering and searching from json file and I am newbie in this technology. So I am little bit confused how to write like query in elasticsearch.

select * from table_name where 'field_name' like 'a%'

This is mysql query. How do I write this query in Elasticsearch? I am using elasticsearch version 0.90.7.

Upvotes: 30

Views: 88091

Answers (4)

Rishabh Pradhan
Rishabh Pradhan

Reputation: 91

Writing a custom bool query worked for me

@Query("{\"bool\":{\"should\":[{\"query_string\":{\"fields\":[\"field_name\"],\"query\":\"?0*\"}}]}}")

Upvotes: 0

Rajitha Bhanuka
Rajitha Bhanuka

Reputation: 842

Below query I wrote, this is something like

SELECT * FROM TABLE WHERE api='payment' AND api_v='v1' AND status='200' AND response LIKE '%expired%' AND response LIKE '%token%'

Please note table = document here

GET/POST both accepted

GET /transactions-d-2021.06.24/_search
{
   "query":{
      "bool":{
         "must":[
            {
               "match":{
                  "api":"payment"
               }
            },
            {
               "match":{
                  "api_v":"v1"
               }
            },
            {
               "match":{
                  "status":"200"
               }
            },
            {
               "wildcard":{
                  "response":"*expired*"
               }
            },
            {
               "wildcard":{
                  "response":"*token*"
               }
            }
         ]
      }
   }
}

Upvotes: 3

Daniel Hoffmann-Mitscherling
Daniel Hoffmann-Mitscherling

Reputation: 2379

I would highly suggest updating your ElasticSearch version if possible, there have been significant changes since 0.9.x.

This question is not quite specific enough, as there are many ways ElasticSearch can fulfill this functionality, and they differ slightly on your overall goal. If you are looking to replicate that SQL query exactly then in this case use the wildcard query or prefix query.

Using a wildcard query:

Note: Be careful with wildcard searches, they are slow. Avoid using wildcards at the beginning of your strings.

GET /my_index/table_name/_search
{
    "query": {
        "wildcard": {
            "field_name": "a*"
        }
    }
}

Or Prefix query

GET /my_index/table_name/_search
{
    "query": {
        "prefix": {
            "field_name": "a"
        }
    }
}

Or partial matching:

Note: Do NOT blindly use partial matching, while there are corner cases for it's use, correct use of analyzers is almost always better.

Also this exact query will be equivalent to LIKE '%a%', which again, could be better setup with correct use of mapping and a normal query search!

GET /my_index/table_name/_search
{
    "query": {
        "match_phrase": {
            "field_name": "a"
        }
    }
}

If you are reading this wondering about querying ES similarly for search-as-you-type I would suggest reading up on edge-ngrams, which relate to proper use of mapping depending on what you are attempting to do =)

Upvotes: 55

Pranav Mishra
Pranav Mishra

Reputation: 197

GET /indexName/table_name/_search
{
    "query": {
        "match_phrase": {
            "field_name": "your partial text"
        }
    }
}

You can use "type" : "phrase_prefix" to prefix or post fix you search Java code for the same: 

AndFilterBuilder andFilterBuilder = FilterBuilders.andFilter();
 andFilterBuilder.add(FilterBuilders.queryFilter(QueryBuilders.matchPhraseQuery("field_name",
          "your partial text")));

Gave 'and filter' example so that you can append extra filters if you want to. Check this for more detail:

https://www.elastic.co/guide/en/elasticsearch/guide/current/slop.html

Upvotes: 4

Related Questions