26

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.

Peter Featherstone
  • 7,835
  • 4
  • 32
  • 64
Dixit Sourav
  • 350
  • 1
  • 3
  • 10
  • 0.90.7 is a very (very) old version of ES. I suggest using the latest release. – Andrei Stefan Jul 02 '15 at 05:26
  • 1
    Also, please read the [documentation](https://www.elastic.co/guide/en/elasticsearch/guide/current/partial-matching.html)! It covers many use cases and offers various solutions to what you need. – Andrei Stefan Jul 02 '15 at 05:45
  • You can use [regexp](https://stackoverflow.com/questions/6467067/how-to-search-for-a-part-of-a-word-with-elasticsearch/51843146#51843146). It worked for me. – Ali Moshiri Aug 16 '18 at 06:41

4 Answers4

51

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

Daniel Hoffmann-Mitscherling
  • 2,339
  • 1
  • 16
  • 23
  • as a kql in kibana you might try `message : "Unable to query openstack for*" and message : "*retry"` to get `Unable to query openstack for*retry` – xiaojueguan Feb 24 '21 at 08:27
4
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

Pranav Mishra
  • 197
  • 3
2

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*"
               }
            }
         ]
      }
   }
}
Rajitha Bhanuka
  • 714
  • 10
  • 11
  • This ELK search query would take forever for an extensive dataset. Ironically, SQL is far faster than these NoSQL queries! – Yahya Jan 07 '23 at 14:43
0

Writing a custom bool query worked for me

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