Filtering on missing field not working

Elastic Stack Elasticsearch
1

In our document "Product" mapping, we have a field object called "codes"- example:

"codes" : [
{
"code" : "1234",
"place_id" : null
},
{
"code" : "4504",
"place_id" : 29
}
]

We are trying to write a query to retrieve all documents that have a codes.place_id that is either null, or another number.

However, this query is not returning any results (only a portion of it below):

  "filter": {
    "and": [
      {
        "or": [
          {
            "missing": {
              "field": "codes.place_id",
              "existence": true,
              "null_value": true
            }
          },
          {
            "in": {
              "codes.place_id": [
                3
              ]
            }
          }
        ]
      }
    ]
  }

If I change the "in" clause to match the document's place_id of 29, then it returns results properly. However, the original query should return results because that document has one code with a place_id of null, right?

Here is the mapping:

{
  "product": {
    "properties": {
      "codes": {
        "properties": {
          "code": {
            "type": "string",
            "index": "not_analyzed",
            "fields": {
              "analyzed": {
                "type": "string"
              }
            }
          },
          "place_id": {
            "type": "long"
          }
        }
      }
    }
  }
}
2

try removing

         "existence": true,
         "null_value": true

I think they are only required if you use null_value mapping

3

I've tried every valid combination of existence/null_value :frowning:

4

Another approach would be to use null_value mapping in the mapping document, and then you can remove the entire OR clause and just use an IN clause and include the mapped null value in there. so if you map null to "-99999" then you can just do

{in: {"codes.place_id": [3, -99999]}}