We have a strange behavior with sorting. The value returned for sorting is null.
We decided to add a sub-field lowercase for all fields to support case-insensitive sorting. We did the following:
- Added custom lowercase normalizer to index settings:
"normalizer": {
"lowercase_normalizer": {
"filter": [
"lowercase"
],
"type": "custom"
}
},
- Added
lowercasesub-field for every field in mappings
"fields": {
"lowercase": {
"type": "keyword",
"normalizer": "lowercase_normalizer"
},
E.g. for the field bd_constructionCoordinator_string it looks like this now:
"bd_constructionCoordinator_string": {
"type": "text",
"fields": {
"lowercase": {
"type": "keyword",
"normalizer": "lowercase_normalizer"
},
"raw": {
"type": "keyword"
}
},
"copy_to": [
"all_search_fields"
],
"norms": false
},
- Executed update-by-query for the index to pick up mapping changes.
Now when performing a search with sorting, null is returned as the sorting value for the document instead of its value. But the field and its value exist and it is correct. E.g. query:
{
"_source": {
"excludes": [
"task_*",
"assigneesOrCandidates"
]
},
"from": 0,
"query": {
"bool": {
"filter": [
{
"term": {
"instance_piid_key": {
"value": "08288351-b932-11ee-bc17-0242ac170006"
}
}
}
]
}
},
"size": 20,
"sort": [
{
"bd_constructionCoordinator_string.lowercase": {
"missing": "_last",
"order": "asc",
"unmapped_type": "keyword"
}
},
{
"instance_isroot_boolean": {
"order": "desc",
"unmapped_type": "boolean"
}
}
],
"track_total_hits": false,
"fields": [
"*.lowercase"
]
}
returns
{
"took": 17,
"timed_out": false,
"_shards": {
"total": 1,
"successful": 1,
"skipped": 0,
"failed": 0
},
"hits": {
"max_score": null,
"hits": [
{
"_index": "camunda",
"_type": "_doc",
"_id": "0834912c-b932-11ee-bc17-0242ac170006",
"_score": null,
"_source": {
"instance_camundastatus_key": "ACTIVE",
"instance_isroot_boolean": true,
"bd_managementArea_string": "Texas",
"bd_constructionStartDate_date": 1705847103000,
"bd_managementAreaCode_string": "TX1",
"bd_externalDecimal_double": 123.45,
"bd_externalDate_date": 1614625054913,
"bd_constructionSupervisorUsername_string": "user",
"lastUpdated": 1705933528155,
"bd_constructionSupervisor_string": "Bob Jones",
"bd_regionCode_string": "SC1",
"process_processtemplatename_string": "Simple Process",
"process_processtemplateid_key": "simple:1:1a648e7e-b6d9-11ee-bc17-0242ac170006",
"instance_rootpiid_key": "08288351-b932-11ee-bc17-0242ac170006",
"instance_piid_key": "08288351-b932-11ee-bc17-0242ac170006",
"bd_address_string": "4 Tree St",
"bd_constructionDueDate_date": 1707747903000,
"instance_processinstancename_string": null,
"bd_region_string": "South Central",
"bd_prismState_string": "CIP",
"instance_superpiid_key": null,
"bd_externalBoolean_boolean": true,
"instance_status_key": "Active",
"instance_businesskey_string": "1",
"bd_constructionType_string": "National",
"instance_createdate_date": 1705933503144,
"bd_externalNumber_long": 123,
"bd_jobType_string": "Residential - MDU",
"bd_constructionManagerUsername_string": "jbob",
"bd_tentativeServiceDate_date": 1708611903000,
"bd_constructionCoordinatorUsername_string": "P3",
"bd_externalString_string": "value1",
"bd_prismId_long": 1,
"bd_constructionCoordinator_string": "Jane Doe",
"bd_constructionManager_string": "Jim Bob"
},
"fields": {
"bd_constructionCoordinator_string.lowercase": [
"jane doe"
],
"bd_constructionSupervisor_string.lowercase": [
"bob jones"
],
"task_executionId_key.lowercase": [
"082b68bd-b932-11ee-bc17-0242ac170006"
],
"bd_constructionManagerUsername_string.lowercase": [
"jbob"
],
"bd_jobType_string.lowercase": [
"residential - mdu"
],
"bd_externalString_string.lowercase": [
"value1"
],
"bd_region_string.lowercase": [
"south central"
],
"task_name_string.lowercase": [
"task 1"
],
"task_taskDefinitionKey_key.lowercase": [
"task_1"
],
"bd_managementArea_string.lowercase": [
"texas"
],
"task_assigneetype_key.lowercase": [
"group"
],
"task_status_key.lowercase": [
"active"
],
"bd_constructionCoordinatorUsername_string.lowercase": [
"p3"
],
"process_processtemplatename_string.lowercase": [
"simple process"
],
"instance_businesskey_string.lowercase": [
"1"
],
"instance_rootpiid_key.lowercase": [
"08288351-b932-11ee-bc17-0242ac170006"
],
"process_processtemplateid_key.lowercase": [
"simple:1:1a648e7e-b6d9-11ee-bc17-0242ac170006"
],
"bd_regionCode_string.lowercase": [
"sc1"
],
"bd_address_string.lowercase": [
"4 tree st"
],
"bd_constructionSupervisorUsername_string.lowercase": [
"user"
],
"task_tkiid_key.lowercase": [
"0834912c-b932-11ee-bc17-0242ac170006"
],
"instance_status_key.lowercase": [
"active"
],
"task_candidategroups_key.lowercase": [
"user"
],
"instance_piid_key.lowercase": [
"08288351-b932-11ee-bc17-0242ac170006"
],
"bd_constructionType_string.lowercase": [
"national"
],
"instance_camundastatus_key.lowercase": [
"active"
],
"bd_prismState_string.lowercase": [
"cip"
],
"bd_constructionManager_string.lowercase": [
"jim bob"
],
"bd_managementAreaCode_string.lowercase": [
"tx1"
]
},
"sort": [
null,
1
]
}
]
}
}
Take a look into bd_constructionManager_string.lowercase field and into sort in the response.
The issue is affected only by existing documents. The newly added documents work well. Normal value is returned for them.
Have we done something wrong or is this some ES defect? If more data is needed I can try to provide it.
The Elasticsearch version is 7.17.10