Search ElasticSearch field contained in a value

Question

I'm trying to run similar field query in ElasticSearch:

select * from products where 'milk' like '%'+name+'%'

Meaning I'm trying to find all the documents that the product name in this case is a sub string of 'milk'.

How can I do it?

I'm not sure how it can help I don't need: text LIKE "%quick%", I need the opposite (see the question — Guy Korland
Can you elaborate with an example. Something like 3 docs with product names: abcmilkman, abcman, a man etc. You want the first 2 docs to be returned as the result? If so, then the sql query seems a bit off, i mean shouldn't it be select * from products where 'product_name' like '%'+milk+'%'? Or have i got it all wrong — Archit Saxena
@ArchitSaxena on that is not what I need. e.g. if I have a docs with the product names: 'bc', 'cd' & 'def' and the query is for 'abcde', I would like to get the first two docs, exactly like in SQL select * from products where 'abcde' like '%' + product_name + '%' — Guy Korland
Can you explain what name is in '%'+name+'%'? Are you somehow constructing this SQL query using some programming language and you're concatenating different parts of the query together? Because + is not a valid operator in SQL. — Val

Val Val · Accepted Answer · 2016-06-07T11:39:29

I would go with a custom analyzer leveraging ngrams. First create an index like this:

curl -XPUT 'localhost:9200/tests' -d '
{
    "settings" : {
        "analysis" : {
            "analyzer" : {
                "my_analyzer" : {
                    "tokenizer" : "ngrams"
                }
            },
            "tokenizer" : {
                "ngrams" : {
                    "type" : "nGram",
                    "min_gram" : "2",
                    "max_gram" : "10"
                }
            }
        }
    },
    "mappings": {
        "test": {
            "properties": {
                "product_name": {
                    "type": "string",
                    "analyzer": "standard",
                    "search_analyzer": "my_analyzer"
                }
            }
        }
    }
}'

Then you can index some data:

curl -XPOST 'localhost:9200/tests/test/_bulk' -d '
{"index":{}}
{"product_name": "bc"}
{"index":{}}
{"product_name": "cd"}
{"index":{}}
{"product_name": "def"}
'

And finally you can search like this:

curl -XPOST 'localhost:9200/tests/_search' -d '{
    "query": {
        "match": {
            "product_name": "abcde"
        }
    }
}'

And you'll get the first two documents bc and cd

Search ElasticSearch field contained in a value

1 Answers