ElasticSearch - query documents where the nested field is empty array []

Viewed 201

I am trying to filter by the empty arrays of the nested field. I tried many different commands, even scripts, and flattened fields, but couldn't retrieve any results. Does anyone has experience with this, is it possible to be done in the ES? I also want to aggregate (count results) by the same empty array field value []

  • mapping
     suitability:
        type: "nested"
        properties:
            group:
               type: "keyword"
            code:
               type: "keyword"

in the index, I have this nested field in every document

"suitability": [
            {
                "group": "RG309",
                "code": 1
            },
            {
                "group": "RG318",
                "code": 1
            },
            {
                "group": "RG355",
                "code": 2
            }
]

also some documents have an empty nested field

"suitability": []
  • query for empty suitability results ( DOESN'T WORK - always return total_hits: 0)
GET /_search
{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "ignore_unmapped": [
                            true
                        ],
                        "path": "suitability",
                        "query": {
                            "bool": {
                                "must_not": [
                                    {
                                        "exists": {
                                            "field": "suitability"
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            ]
        }
    },
    "track_total_hits": true
}
  • query for not empty suitability ( THIS WORKS: returns all results )
{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "ignore_unmapped": [
                            true
                        ],
                        "path": "suitability",
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                         "terms": {
                                             "suitability.rule_result": [
                                                 "1",
                                                 "2",
                                                 "3"
                                             ]
                                         }
                                     }
                                 ]
                            }
                        }
                    }
                }
            ]
        }
    },
    "track_total_hits": true
}
0 Answers
Related