ElasticSearch: Query number field to use like clause as we do for string field

Question

I have 2 fields in my Elasticsearch cache that I created using Oracle river-jdbc.
First column is numeric and second is string as name.

I want to use this indexing in autocomplete textbox using jQuery.

All implementation is done for the name field.
User can provide any string (at least 3 characters) then hit goes to Elasticsearch with the given string and searches data as it is "In-String" part of name field and returns the result. Similarly to querying in SQL using the LIKE operator for name field and it's working and data is loaded in the UI.

I want to do the same with the numeric field, but until and unless I give complete value of the numeric field Elasticsearch doesn't return any data. So autocomplete does not works for numeric field.

Below is the code:

Creating river field as:

{
    "type": "jdbc",
    "jdbc": {
        "driver": "oracle.jdbc.driver.OracleDriver",
        "url": "jdbc:oracle:thin:@//<ip-addr>:1521/db",
        "user": "user",
        "password": "pwd",
        "sql": "select curr_duns_number as duns, TRIM(name) as company from subject where rownum < 10000"
    },
    "index": {
        "index": "subject",
        "type": "name"
    },
    "properties": {
        "duns": {"type": "string", "store": "yes"},
        "company": {"type": "string"}
    }
}

Fetching company field:

POST http://<ip-addr>:9200/subject/name/_search
{
    "from": 0,
    "size": 10,
    "query": {
        "wildcard": {
            "COMPANY": "boo*"
        }
    },
    "sort": [
        {
            "COMPANY": {"order": "asc"}
        }
    ]
}

After trying various combinations like wildcard, matching, and query_string it doesn't give me results, and I'm left with the following problems:

I cannot query numeric fields in a similar way to how it's done using SQL, e.g. select * from subject where curr_duns_number like '%123%';
Sorting order is not properly applied as the token for sorting Elasticsearch is considering is usually a word from company name.

Do you know that this is case sensitive? So, instead of searching for 'COMPANY', you should search for 'company'. Same for sorting by the way. See also [When is case sensitivity important in JSON requests to ASP.NET web services (ASMX)?](http://stackoverflow.com/questions/2738321/when-is-case-sensitivity-important-in-json-requests-to-asp-net-web-services-asm) — Roeland Van Heddegem, Jun 23 '15 at 11:22

score 1 · Accepted Answer · answered Jul 23 '13 at 07:57

Well after too much research I could not find any answer on this. As a solution I changed the type of numeric field into string by appending a string to it and achieve the auto-completion for this project.

For the sake of closing the question I am accepting this answer but if any solution comes in future I will add or any one can add his comments to it.

Thanks

ElasticSearch: Query number field to use like clause as we do for string field

1 Answers1