# Query based on array items satisfiying certain criteria

**URL:** <https://discuss.elastic.co/t/query-based-on-array-items-satisfiying-certain-criteria/194599>\
**Category:** Elasticsearch\
**Created:** [August 9, 2019, 11:12am UTC](https://discuss.elastic.co/t/query-based-on-array-items-satisfiying-certain-criteria/194599 "2019-08-09T11:12:21Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sai.khaled](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sai.khaled/32/52023_2.png) [@sai.khaled](https://discuss.elastic.co/u/sai.khaled)\
**Post date:** [August 9, 2019, 11:12am UTC](https://discuss.elastic.co/t/query-based-on-array-items-satisfiying-certain-criteria/194599/1 "2019-08-09T11:12:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\
**Post date:** [August 12, 2019, 9:34am UTC](https://discuss.elastic.co/t/query-based-on-array-items-satisfiying-certain-criteria/194599/2 "2019-08-12T09:34:26Z")

</div>

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html). That's not going to work. You need to wrap your `must_not` query in a `nested` query:

```auto
{
  "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 🙂)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 9, 2019, 9:48am UTC](https://discuss.elastic.co/t/query-based-on-array-items-satisfiying-certain-criteria/194599/3 "2019-09-09T09:48:49Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.