Query based on array items satisfiying certain criteria

[sai.khaled]

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

[abdon (Abdon Pijpelink)]

I think there's a couple of things going on here.

First of all, "dynamic": "false" in your mapping will cause Elasticsearch to silently ignore any fields that are unmapped. As a result, clientIds.endDate will never ever be a hit for an exist query. Elasticsearch will just ignore that field because it has not been explicitly mapped. You probably want to remove "dynamic": "false" from your mapping, or provide a mapping for the nested fields.

More importantly, you are trying to query a nested field without using the nested query. That's not going to work. You need to wrap your must_not query in a nested query:

{
  "from": 0,
  "query": {
    "bool": {
      "must": [
        {
          "prefix": {
            "lastname": {
              "value": "dupont"
            }
          }
        },
        {
          "nested": {
            "path": "clientIds",
            "query": {
              "bool": {
                "must_not": {
                  "exists": {
                    "field": "clientIds.endDate"
                  }
                }
              }
            }
          }
        }
      ]
    }
  },
  "size": 15,
  "sort": [
    {
      "lastname.keyword": {
        "order": "asc"
      }
    },
    {
      "firstname.keyword": {
        "order": "asc"
      }
    }
  ]
}

(By the way, when posting code snippets on this forum, please use the </> button to format the code. That makes the code much easier to read, and as a result much easier for folks to help you )