How to use Jason input in Y axis to filter null values

[qub123 (kenneth lam)]

Hi ,

I would like to filter null value on a Y-axis for string type or date type in the advanced option “Json input “ . Just wondering how should I do it. I don’t want to do it on the “filter” part because I want all my y axis has OR condition. I am having error when putting script below

example: { "script" : "doc['job_id'].value = null" }

example: { "script" : "doc['job_date'].value = null" }

[Aaron_Caldwell]

Hello Kenneth,

It sounds like you want a bool query using must and should to effectively give you the OR logic you're after. When you create a filter, you can expand the filter to write custom Query DSL logic. From the documentation: "If the bool query is a filter context or has neither must or filter then at least one of the should queries must match a document for it to match the bool query"

You'll find several examples of how to do this around in general, but to get you started:

• https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-bool-query.html
• https://stackoverflow.com/questions/28538760/elasticsearch-bool-query-combine-must-with-or

Regards,
Aaron

[qub123 (kenneth lam)]

Hi Aaron,

Thanks for your reply! But it is not about filtering, should or must or must_not. It is not what I want to achieve, I tried all these options, didn't get the result i wanted. I want to know if "not_exist" is supported?

I explained a bit on my scenario below, I have some ES doc that contains different fields, some with null values, some with values. I want to get the count of the field for "not_exists" values. Please check my example below.

Wanted Behavior
"bool": {
"should": [
{
"not_exists": {
"field": "queue_event_id"
}
},
{
"not_exists": {
"field": "start_event_id"
}
},
{
"not_exists": {
"field": "finish_event_id"
}
}
]
}

Elastic Search Documents Example
ES doc1
domain_id = host123
queue_event_id = a123
start_event_id = a234
finish_event_id = a345

ES doc2
domain_id = host123
queue_event_id = b123
start_event_id = b234
finish_event_id = b345

ES doc3
domain_id = host123
queue_event_id = null
start_event_id = c234
finish_event_id = null

ES doc4
domain_id = host123
queue_event_id = d123
start_event_id = null
finish_event_id = null

My Kibana Graph Configuration
I have 3 y axis in my graph
y1-uniquecount (queue_event_id)
y2-uniquecount (start_event_id)
y3-uniquecount (finish_event_id)

x axis
x-singnificant term (domain_id)

My Expected Result
expected result for those id that contains "null" value for domain host123 is

---

number of not_exist --- queue_event_id = 1
number of not_exist --- start_event_id = 1
number of not_exist --- finish_event_id = 2

The query below with "must_not" will give me 0 result for everything, since it is AND condition. If I want to use "should" condition, then I need a "not_exists" tag , but not_exist tag is not supported

{
  "query": {
    "bool": {
      "must_not": [
        {
          "exists": {
            "field": "queue_event_id"
          }
        },
        {
          "exists": {
            "field": "start_event_id"
          }
        },
        {
          "exists": {
            "field": "finish_event_id"
          }
        }
      ]
    }
  }
}