Where to add a missing filter in this Kibana search

Hi all... I'm a newbie so excuse me, but I've a doubt about filtering searches in order to not show a null or non valued field.

We want something like:

 "filter" : {
        "missing" : {
            "field" : "COORDS"
        }
    }

Where to put the missing or exists field we want to filter in this kibanaSavedObjectMeta.searchSourceJSON?

{
  "index": "metrics",
  "query": {
    "query_string": {
      "analyze_wildcard": true,
      "query": "*"
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  },
  "filter": [
    {
      "meta": {
        "disabled": false,
        "index": "metrics",
        "key": "_type",
        "negate": false,
        "value": "TRANSFERENCIAS_NACIONALES"
      },
      "query": {
        "match": {
          "_type": {
            "query": "TRANSFERENCIAS_NACIONALES",
            "type": "phrase"
          }
        }
      }
    }
  ]
}

Thanks in advance!

I don't completely understand what you're trying to do - could you explain our use case in more detail?

The Missing Filter in Elasticsearch does the opposite of what you described - it returns documents that are missing a specific field.

In general, if you want to use a Missing Filter query DSL in Kibana, you should wrap the filter into a constant_score query. Instead of inserting this into the .kibana index directly, I'd recommend pasting the JSON directly the search bar in Discover and then saving that query using the UI (see screenshot) -- it's much less error prone. You can then examine the object and see its structure in Settings >> Objects, if you wish (see mine below).

{
  "index": "logstash-*",
  "query": {
    "constant_score": {
      "filter": {
        "missing": {
          "field": "bytes"
        }
      }
    }
  },
  "highlight": {
    "pre_tags": [
      "@kibana-highlighted-field@"
    ],
    "post_tags": [
      "@/kibana-highlighted-field@"
    ],
    "fields": {
      "*": {}
    },
    "fragment_size": 2147483647
  },
  "filter": []
}

1 Like

Thanks Tanya! We've tried to do as you said, but the result is the same...

We want to get all the documents in one index with "coordenadasDestino" != NULL ... in all our documents this field exists but sometime its NULL or doens't have values

If we do:

{"constant_score":{"filter":{"exists":{"field":"coordenadasDestino"}}}}*

We get all the documents including also the ones who hasn't values for field coordenadasDestino (NULL)

How can we do this?

Thanks in advance! Regards.

You shouldn't need the star at the end, the following should work:
{"constant_score":{"filter":{"exists":{"field":"coordenadasDestino"}}}}

1 Like

Thanks Tanya... it works but not as we expected... because in the spark code to inject to Elastic we put a "" into "coordenadasDestino instead putting [NULL]

We'll change the scala code in order to put NULL there. Thanks! Regads.