How to Do "Where Not Exists" Type Filtering in Kibana/elk?

Better Stack Team
Updated on October 26, 2024

In Kibana and Elasticsearch, you can perform a "WHERE NOT EXISTS" type of filtering (i.e., finding documents where a field does not exist) by using a must_not clause in an Elasticsearch query or applying the appropriate filter in Kibana's interface.

1. Using the Kibana Discover Tab

Steps:

  1. Open the Discover Tab:
    • In Kibana, go to the Discover tab.
  2. Add a Filter for Non-Existent Fields:
    • At the top of the Discover tab, click Add a filter.
    • In the Field dropdown, select the field you want to filter by (e.g., user_id, email, etc.).
    • In the operator dropdown, select "is not".
    • In the value dropdown, select "exists".

This will filter out all documents where the field exists, effectively returning documents where the field does not exist.

Example:

  • If you want to filter documents where the field email does not exist, you would select:
    • Field: email
    • Operator: is not
    • Value: exists

This creates a "WHERE NOT EXISTS" condition for the field email.

2. Using KQL (Kibana Query Language) in Discover/Visualizations

You can use the Kibana Query Language (KQL) in the search bar to filter out documents where a specific field doesn't exist.

KQL Syntax:

To find documents where a field does not exist, you can use the following syntax:

 
!field_name: *

Example:

To find documents where the field email does not exist, you would type:

 
!email: *

This will filter out all documents where the email field is present, leaving only those where the field does not exist.

3. Using Lucene Query Syntax in Discover/Visualizations

Alternatively, if you're using Lucene query syntax in Kibana, you can perform a similar operation by using the following query:

 
-_exists_:field_name

Example:

To filter documents where the field email does not exist, you would type:

 
-_exists_:email

This filters out all documents where the email field exists.

4. Using Elasticsearch Query DSL in Dev Tools

If you want to perform the query directly using the Elasticsearch Query DSL via Dev Tools, you can use the must_not clause to achieve the "WHERE NOT EXISTS" filter.

Query Syntax:

 
GET /your_index/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "your_field_name"
        }
      }
    }
  }
}

Example:

To retrieve documents where the field email does not exist:

 
GET /your_index/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "email"
        }
      }
    }
  }
}

This query will return all documents where the email field does not exist.

Summary:

  • In Kibana Discover: Use filters with "is not" exists to filter documents where the field does not exist.
  • Using KQL: Use the !field_name: * syntax to exclude documents where the field exists.
  • Using Lucene: Use _exists_:field_name to filter out documents where the field exists.
  • In Elasticsearch DSL: Use the must_not clause with an exists query in Elasticsearch's Query DSL for more advanced queries.

These methods give you flexibility to filter documents in Kibana or Elasticsearch where specific fields are missing, which is equivalent to a SQL-style "WHERE NOT EXISTS" condition.

Got an article suggestion? Let us know
Explore more
Licensed under CC-BY-NC-SA

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Make your mark

Join the writer's program

Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.

Write for us
Writer of the month
Marin Bezhanov
Marin is a software engineer and architect with a broad range of experience working...
Build on top of Better Stack

Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.

community@betterstack.com

or submit a pull request and help us build better products for everyone.

See the full list of amazing projects on github