Hi, everyone. I’m a quick, quick, quick, quick

Setting the default value of a table to Null is not recommended in MySQL, and there will be a separate article on this later.

However, there are a lot of fields that default to Null in your business before you enter a new company. Importing these values into ElasticSearch still needs to be handled. Let’s see how ElasticSearch handles Null values.

How does ElasticSearch handle Null values

So let’s look at an example, what happens when the value is null

POST /kaka/_bulk
{ "index": { "_id": "1"}}
{ "tags" : ["search"]}  
{ "index": { "_id": "2"}}
{ "tags" : ["search", "open_source"] }  
{ "index": { "_id": "3"}}
{ "tags" : null                      }  
{ "index": { "_id": "4"}}
{ "tags" :"null"}
Copy the code

In this case, you can see that there is a null value for both 3 and 4, except that one is a string NULL

post /kaka/_search
{
  "query":{
    "term": {
      "tags": null
    }
  },
  "profile":"true"
}
Copy the code

The following error occurs when you perform the above search

{
  "error": {
    "root_cause": [
      {
        "type": "illegal_argument_exception",
        "reason": "field name is null or empty"
      }
    ],
    "type": "illegal_argument_exception",
    "reason": "field name is null or empty"
  },
  "status": 400
}
Copy the code

Void values cannot be indexed or searched in ElasticSearch. Empty arrays and null arrays are treated as if they have no value

If you execute the following statement, the last item of data is returned

post /kaka/_search
{
  "query":{
    "term": {
      "tags": "null"
    }
  },
  "profile":"true"
}
Copy the code

2. Use exists to resolve the Null value search problem in ElasticSearch

The answer is also found in the documentation, as follows

post /kaka/_search
{
  "query":{
    "constant_score": {
      "filter": {
        "missing": {
          "field": "tags"
        }
      }
    }
  }
}
Copy the code

[query] registered for [missing] No [query] registered for [missing] An exists exists and does not exist, according to the document

Do not set exists to null

post /kaka/_search
{
  "query":{
    "constant_score":{
      "filter":{
        "exists":{
          "field":"tags"
        }
      }
    }
  }
}
Copy the code

The exists query is null

post /kaka/_search
{
  "query":{
    "bool":{
      "must_not":{
        "exists":{
          "field":"tags"
        }
      }
    }
  }
}
Copy the code

Replace the null value with null_value

Delete index delete kaka, then customize the mapping, set the tags to “NULl_value” : “null”, and replace the displayed null value with the specified value. “NULL” can be any value

Null_value has the advantage that empty fields can be indexed without the error that field name is null or empty

put kaka
{
  "mappings":{
    "properties":{
      "tags" :{
        "type":"keyword",
        "null_value":"null"
      }
    }
  }
}
Copy the code

Insert the data above

POST /kaka/_bulk
{ "index": { "_id": "1"}}
{ "tags" : ["search"]}  
{ "index": { "_id": "2"}}
{ "tags" : ["search", "open_source"] }  
{ "index": { "_id": "3"}}
{ "tags" : null                      }  
{ "index": { "_id": "4"}}
{ "tags" :"null"}
Copy the code

Execute the null data again, and the third and fourth data items appear

post /kaka/_search
{
  "query":{
    "term": {
      "tags": "null"
    }
  },
  "profile":"true"
}
Copy the code

Use null_value

Null_value must match the defined data type. For example, value_NULL of type long cannot be defined as a string

What can happen if value_null is set to long

# error demo, The long type uses a null_value of the string type. Put kaka {"mappings":{"properties":{"tags" :{"type":"long", "null_value":"null"}}}}Copy the code

The following error is returned

{
  "error": {
    "root_cause": [
      {
        "type": "mapper_parsing_exception",
        "reason": "Failed to parse mapping [_doc]: For input string: "null""
      }
    ],
    "type": "mapper_parsing_exception",
    "reason": "Failed to parse mapping [_doc]: For input string: "null"",
    "caused_by": {
      "type": "number_format_exception",
      "reason": "For input string: "null""
    }
  },
  "status": 400
}
Copy the code

Note that value_NULL is not valid for any type. Null_value can be used for any of the following types

  • Array
  • Boolean
  • Date
  • geo_point
  • ip
  • keyword
  • Numeric
  • point

“

Insist on learning, insist on writing, insist on sharing is the belief that Kaka has been upholding since he started his career. May the article in the big Internet can give you a little help, I am kaka, see you next time.

“