Query nested array for empty and specific value

Documents structure -

{
  "hits": [
    {
      "_type": "_doc",
      "_id": "ef0a2c44179a513476b080cc2a585d95",
      "_source": {
        "DIVISION_NUMBER": 44,
        "MATCHES": [
          {
            "MATCH_STATUS": "APPROVED",
            "UPDATED_ON": 1599171303000
          }
        ]
      }
    },
    {
      "_type": "_doc",
      "_id": "ef0a2c44179a513476b080cc2a585d95",
      "_source": {
        "DIVISION_NUMBER": 44,
        "MATCHES": [ ]
      }
    }
  ]
}

Question - MATCHES is a nested array inside there is a text field MATCH_STATUS that can have any values say "APPROVED","REJECTED".
I am looking to search ALL documents that contain MATCH_STATUS having values say "APPROVED", "RECOMMENDED" as well as where there is no data in MATCHES (empty array "MATCHES": [ ] ). Please note I want this in a single query.

I am able to do this in two separate queries like this -
GET all matches with status = RECOMMENDED, APPROVED

"must": [
        {
          "nested": {
            "path": "MATCHES",
            "query": {
              "terms": {
                "MATCHES.MATCH_STATUS.keyword": [
                  "APPROVED",
                  "RECOMMENDED"
                ]
              }
            }
          }
        }
      ]

GET all matches having empty array "MATCHES" : [ ]

{
  "size": 5000,
  "query": {
    "bool": {
      "filter": [],
      "must_not": [
        {
          "nested": {
            "path": "MATCHES",
            "query": {
              "exists": {
                "field": "MATCHES"
              }
            }
          }
        }
      ]
    }
  },
  "from": 0
}

Hello @kunwarshukla

If you using "should", maybe works.

{
   "query":{
      "bool":{
         "should":[
            {
               "bool":{
                  "must_not":[
                     {
                        "nested":{
                           "path":"MATCHES",
                           "query":{
                              "exists":{
                                 "field":"MATCHES"
                              }
                           }
                        }
                     }
                  ]
               }
            },
            {
               "bool":{
                  "must":[
                     {
                        "nested":{
                           "path":"MATCHES",
                           "query":{
                              "terms":{
                                 "MATCHES.MATCH_STATUS.keyword":[
                                    "APPROVED",
                                    "RECOMMENDED"
                                 ]
                              }
                           }
                        }
                     }
                  ]
               }
            }
         ]
      }
   }
}

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