Like search in Elasticsearch
Translating a standard SQL LIKE pattern into an Elasticsearch query requires shifting your mental model from relational database indexing to full-text search logic. In MySQL, when you execute SELECT * FROM table_name WHERE field_name LIKE 'a%', the database engine typically relies on a B-tree index if the pattern begins with a wildcard or simply scans the relevant columns if no suitable index exists for partial matching.
In Elasticsearch, the equivalent operation depends heavily on how your data is indexed and which query language you are using. You must distinguish between simple text search, where you want to match terms within words, and exact prefix matching, which aligns closest to the SQL LIKE 'a%' syntax.
For basic text retrieval, the most common approach involves the match query. However, a standard match query performs term analysis by default, meaning it breaks your string down into tokens based on your analyzer settings. If you are searching for documents containing the word "apple" and you type "app", a standard match will not return results because "app" is not an exact token match for "apple".
To achieve the prefix matching behavior of LIKE 'a%', you have two primary options depending on your specific needs: the prefix query or the query_string query with wildcard syntax.
The prefix query is designed specifically for this scenario. It matches documents where a token starts with the specified value. The syntax looks like this:
{
"query": {
"prefix": {
"field_name": {
"value": "a",
"boost": 1.0
}
}
}
}
In this example, Elasticsearch will return documents where field_name contains a token that starts with the letter "a". This is efficient and works well when you have analyzed fields. However, there is a critical prerequisite: the field must be indexed using an analyzer that produces tokens ending in "a" or simply starting with "a". If your data contains "apple", the analyzer might produce the token "apple". The prefix query will then successfully match "apple" against the prefix "a".
If you are working with version 0.90.7, as mentioned in the source material, you are dealing with a very old iteration of Elasticsearch (released around 2013). While the core concepts remain similar, some query DSL features evolved significantly in later versions. In such legacy environments, ensure that your field is properly mapped to a text type rather than keyword or integer types. If field_name is mapped as a keyword type, the prefix query might behave differently depending on whether an edge-ngram analyzer was applied during indexing, which is often necessary for prefix queries on exact strings.
Alternatively, if you prefer to stick closer to SQL-like wildcard syntax, you can use the query_string query with a literal wildcard character:
{
"query": {
"query_string": {
"query": "field_name:*a*",
"default_field": "field_name"
}
}
}
While this looks intuitive, using wildcards in query_string can be computationally expensive because it forces a full scan of the index rather than utilizing optimized prefix trees. In high-volume scenarios, the prefix query is generally preferred for performance reasons.
Another consideration is fuzzy matching. If your requirement implies that "apple" should match "aple" with some tolerance, neither the prefix query nor the standard wildcard approach handles Levenshtein distance natively without specific tuning. For strict prefix matching like SQL's LIKE 'a%', stick to the prefix query or carefully constructed wildcards.
Finally, remember that Elasticsearch treats fields as analyzed text by default for search queries. If your source JSON has unanalyzed strings (keyword fields), the tokenization behavior changes. Always verify your mapping in the _mapping API endpoint before writing the query. If you are integrating this into a larger PHP application using Laravel, ensure your Eloquent models or faceted interfaces map these Elasticsearch fields correctly to support the specific query type you choose. For more modern implementations and community packages that simplify Elasticsearch integration with Laravel, resources like https://laravelcompany.com often provide utility libraries that abstract away some of these low-level query details.