Handling Null Values

[sidra_89 (Sidra Farooq)]

Hi ..

i want to search where the field value is either equal to null or have some value

like suppose if the field name is brand_name then i want to get record where brand_name is either equal to null (null means no value) or equal to some brand name

exists works okay alone but when i combine it with where is have some particular values it stops working

i need a bool query for it ..

i tried this query

 "query": {

"bool": {
  "must_not": [
      {
        "exists": {
          "field": "brand_name"
        }
      }
    ],
        
    "must": [
      {
        "match": {
            "brand_name": "hello world"
        }
      }
    ]
   }

  }

}

must and must_not alone works fine but when used together in this pattern no results are returned (i am trying to get where brand_name is "hello world" or where brand_name is empty (a null value)

Thank you ..

[forloop (Russ Cam)]

Take a look at building a bool query with two should clauses. You can use a bool query as one of the should clauses in order to invert an exists query using a must_not clause

{
  "query": {
    "bool" : {
      "should" : [
        { "bool" : { 
            "must_not" : [
              { "exists": { "field" : "brand_name" } } 
            ] 
          } 
        },
        { "term" : { "brand_name" : "some brand name" } }
      ]
    }
  }
}

[sidra_89 (Sidra Farooq)]

Thank you so much it solved the problem ..

[sidra_89 (Sidra Farooq)]

one more question if i want to and its result to places where user_ids are 9, 10, 16, 19 how would i integrate this into this??

[forloop (Russ Cam)]

put the bool query with the should clauses in the must clause of an outer bool query, along with a terms query for the user_ids

{
  "query": {
    "bool": {
      "must": [
      {
        "bool": {
          "should": [
          {
            "bool": {
              "must_not": [
              {
                "exists": {
                  "field": "brand_name"
                }
              }]
            }
          },
          {
            "term": {
              "brand_name": "some brand name"
            }
          }]
        }
      },
      {
        "terms": {
          "user_ids": [9, 10, 16, 19]
        }
      }]
    }
  }
}

[sidra_89 (Sidra Farooq)]

okay it worked perfectly fine ..

i get how it is working now .. a bit confuse when to use the bool keyword ..

[forloop (Russ Cam)]

bool queries can be arbitrarily nested, allowing the expression of more complex compound queries