Elasticsearch Search With Multi Fields

How can i create body for elasticsearch like this select * from table where full_name like '%q%' or address like '%q%' or description like '%q%' order by full_name , description ,

Solution 1:

A wildcard query can be very expensive, especially if you search in several fields. The right way to do this is by using an nGram token filter on the fields you want to search only a part of.

First you create an index like below with a custom analyzer that will slice and dice your fields into searchable tokens:

curl -XPUT localhost:9200/tests -d '{
  "settings": {
    "analysis": {
      "analyzer": {
        "substring_analyzer": {
          "tokenizer": "standard",
          "filter": ["lowercase", "substring"]
        }
      },
      "filter": {
        "substring": {
          "type": "nGram",
          "min_gram": 1,
          "max_gram": 15
        }
      }
    }
  },
  "mappings": {
    "test": {
      "properties": {
        "full_name": {
          "type": "string",
          "analyzer": "substring_analyzer"
        },
        "address": {
          "type": "string",
          "analyzer": "substring_analyzer"
        },
        "description": {
          "type": "string",
          "analyzer": "substring_analyzer"
        }
      }
    }
  }
}'

Then you can index a few docs:

curl -XPUT localhost:9200/tests/test/_bulk -d '
{"index":{"_id": 1}}
{"full_name": "Doe", "address": "1234 Quinn Street", "description": "Lovely guy"}
{"index":{"_id": 2}}
{"full_name": "Brennan", "address": "4567 Main Street", "description": "Not qualified"}
{"index":{"_id": 3}}
{"full_name": "Quantic", "address": "1234 Quinn Street", "description": "New friend"}
'

Finally, you can search with a query equivalent to your SQL query above and all three test documents will match:

curl -XPUT localhost:9200/tests/test/_search -d '{
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "full_name": "q"
          }
        },
        {
          "match": {
            "address": "q"
          }
        },
        {
          "match": {
            "description": "q"
          }
        }
      ]
    }
  }
}'

Solution 2:

You can try the following. . . https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-wildcard-query.html `

{"wildcard":{"user":"ki*y"}}

`