How search in firsts characters

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%'