Elasticsearch not analyzed not working in sorting

Question

I have added the mappings for not analyzed option in elasticsearch, it is not working when i sort the results, here is my mapping shows when i use http://localhost:9200/_river/jdbc/_search

 "mappings": {
    "jdbc": {
        "dynamic_templates": [
            { "notanalyzed": {
                  "match":              "*", 
                  "match_mapping_type": "string",
                  "mapping": {
                      "type":        "string",
                      "index":       "not_analyzed"
                  }
               }
            }
          ]
       }
   }  

But when i sort the reults it will take the results wrongly like

http://localhost:9200/jdbc/_search?pretty=true&sort=field:asc

{ 
    field: "McDermott Will Amery", 
},
sort: [
    "amery"
]
}

But i need order the results from A-Z from the starting word in the field

UPDATE: River specification in meta

http://localhost:9200/_river/jdbc/_meta

{
  "_index": "_river",
  "_type": "jdbc",
  "_id": "_meta",
  "_version": 1,
  "found": true,
  "_source": {
    "type": "jdbc",
    "jdbc": {
      "driver": "com.mysql.jdbc.Driver",
      "url": "jdbc:mysql://localhost:3306/dbname",
      "user": "user",
      "password": "pass",
      "sql": "SQL QUERY",
      "poll": "24h",
      "strategy": "simple",
      "scale": 0,
      "autocommit": true,
      "bulk_size": 5000,
      "max_bulk_requests": 30,
      "bulk_flush_interval": "5s",
      "fetchsize": 100,
      "max_rows": 149669,
      "max_retries": 3,
      "max_retries_wait": "10s",
      "locale": "in",
      "digesting": true
    },
    "mappings": {
      "jdbc": {
        "dynamic_templates": [
          {
            "notanalyzed": {
              "match": "*",
              "match_mapping_type": "string",
              "mapping": {
                "type": "string",
                "index": "not_analyzed"
              }
            }
          }
        ]
      }
    }
  }
}

Answer 1

I think your configuration is not correct for what you want to do. Let's start over. First, let's delete your _river index and we'll create it from scratch again:

curl -XDELETE localhost:9200/_river

Now let's create it again but this time by using the correct configuration, i.e.:

1. your mapping needs to be in the jdbc.type_mapping field
2. you need to specify a target index and typewhere your data is going to be stored

Here is how it would look like

curl -XPUT 'localhost:9200/_river/jdbc/_meta' -d '{
    "type" : "jdbc",
    "jdbc": {
      "driver": "com.mysql.jdbc.Driver",
      "url": "jdbc:mysql://localhost:3306/dbname",
      "user": "user",
      "password": "pass",
      "sql": "SQL QUERY",                  <-- add your SQL query
      "poll": "24h",
      "strategy": "simple",
      "scale": 0,
      "autocommit": true,
      "bulk_size": 5000,
      "max_bulk_requests": 30,
      "bulk_flush_interval": "5s",
      "fetchsize": 100,
      "max_rows": 149669,
      "max_retries": 3,
      "max_retries_wait": "10s",
      "locale": "in",
      "digesting": true,
      "index": "your_index",               <-- add this
      "type": "your_type",                 <-- add this
      "type_mapping": {                    <-- add your mapping here
          "your_type": {                   <-- match this with "type" above
            "dynamic_templates": [{
               "notanalyzed": {
                  "match": "*",
                  "match_mapping_type": "string",
                  "mapping": {
                     "type": "string",
                     "index": "not_analyzed"
                  }
               }
            }]
         }
      }
    }
}'

Then when your SQL query runs, it will store data inside the your_index index and use the your_type mapping type.

Finally, you can search your data with the following query:

 curl -XGET 'http://localhost:9200/your_index/your_type/_search?pretty=true&sort=field:asc'

UPDATE

You may also use the following mapping defining a multi-field. Then you'd be able to sort on the not_analyzed field and search on the analyzed one:

"dynamic_templates": [{
   "multi": {
      "match": "*",
      "match_mapping_type": "string",
      "mapping": {
         "type": "string",
         "fields": {
            "raw": {
               "type": "string",
               "index": "not_analyzed"
            }
         }
      }
   }
}]

The query on the field named field would then be

curl -XGET 'http://localhost:9200/your_index/your_type/_search?pretty=true&q=field:Luke&sort=field.raw:asc'