Hi, I’m a beginner in opensearch and I was having difficulties in elasticsearch and in this I found a possibility to use it in opensearch.
I wanted to know if I would have a possibility to perform the search in a specific field, for example, the field has 100 characters and I wanted to search for a specific term in the first 30 characters and if you happen to find and return the fields, records found. Is there such a way in opensearch?
1 Like
I found a way to more he brings all the values, and in this way he still couldn’t help me
POST _plugins/_sql
{
“query” : “SELECT if(substr(field, 1, 15) like ‘%key%’, 0, 1), substr(field, 1, 15), id FROM indice”
}
Sorry it’s been a long time. For future reference: i don’t think SQL supports this, SQL has some relevance functions (sql/relevance.rst at bcfda3736f1f71f7e4a6cc16cae50617b0854ec3 · opensearch-project/sql · GitHub), but it doesn’t allow limiting to first n characters.
But it might be possible with painless script Execute Painless script - OpenSearch documentation
@diego As my understanding, your example query is close to what you expect. Have you tried using SUBSTR with LIKE in WHERE clause? Is the following query what you want:
# Find the first 10 characters match %Mozilla%
SELECT *
FROM opensearch_dashboards_sample_data_logs
WHERE SUBSTRING(agent, 1, 10) LIKE '%Mozilla%'