i have the following mapping
"mappings": {
"contact": {
"dynamic": "false",
"_all": {
"enabled": false
},
"properties": {
"civility": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"clientIds": {
"type": "nested"
},
"firstname": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256,
"normalizer": "custom_sort_normalizer"
}
}
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"lastname": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256,
"normalizer": "custom_sort_normalizer"
}
}
where clientIds is NESTED type, here is sample data
"clientIds": [
- {
- "roleId": "3a1d6acf-f34d-4f7d-9618-b618b3373824",
- "roleType": "5",
- "roleName": "Manager",
- "email": "test@test.co",
- "clientId": "78071"}
,* { - "roleId": "af8807dc-8d57-461a-99c2-49458047309c",
- "roleType": "6",
- "roleName": "Investment Manager",
- "email": "test@test.com",
- "clientId": "8305",
- "endDate": "2019-05-24T00:00:00"}
I have the following query
{
"from": 0,
"query": {
"bool": {
"must": [
{
"prefix": {
"lastname": {
"value": "dupont"
}
}
}
],
"must_not": [
{
"exists": {
"field": "clientIds.endDate"
}
}
]
}
},
"size": 15,
"sort": [
{
"lastname.keyword": {
"order": "asc"
}
},
{
"firstname.keyword": {
"order": "asc"
}
}
]
}
I want to retrieve all documents that match lastname prefix and having at least one endDate ( under nested type) that is NULL, i'm not able to achieve this, what's wrong with my query.
Regards