LoginSignup
5
4

More than 3 years have passed since last update.

@michi_wkwk(Waku Michishita)

[メモ]ElasticsearchでIS NULL的な検索をしたい

Last updated at Posted at 2021-03-15

TL;DR

must_notとexistsを組み合わせて実現できる
パフォーマンス面の検証はしていないのであしからず

実行環境

Elasticsearch: 7.9.3
使ったインデックス定義やサンプルデータはこちら

前置き

あるフィールドについて検索条件を設定したとき、そのフィールドにnullがセットされたドキュメントは検索結果に入ってこない
例えば以下のようなインデックスとデータを考える

PUT simple_index
{
    "mappings":
    {
        "_routing": {"required": false},
        "properties":
        {
            "id": {"type": "keyword"},
            "name": {"type": "keyword"},
            "int_value": {"type": "integer"}
        }
    }
}


POST simple_index/_bulk
{"create":{"_index": "simple_index"}}
{ "id": "1","name": "sample_1","int_value": 10}
{"create":{"_index": "simple_index"}}
{ "id": "2","name": "null","int_value": 20}
{"create":{"_index": "simple_index"}}
{ "id": "3","name": "sample_3","int_value": null}

上記のようなデータの場合、int_valueを対象にした以下のような検索をした場合id=3のドキュメントは引っかからない

GET simple_index/_search
{
  "size": 10,
  "query": {
    "bool": 
    {
      "must": [
        {
          "range": {
            "int_value": {
              "lte": 10
            }
          }
        }
      ]
    }
  }
}

また明示的にnullと書かずとも、あるフィールドそのものが欠けて登録された場合も同じような挙動になる。つまり

POST simple_index/_bulk
{"create":{"_index": "simple_index"}}
{ "id": "8","name": "sample_8"}

というドキュメントを登録した場合、このid=8のレコードはint_valueを対象にした検索には引っかからないことになる。

ここまで検索に引っかからないケースの説明をしたが、あるフィールドがNULLであるドキュメントを検索したいようなケースも存在する
今回はそのようなケースに対応するための方策を考えていく

null_value

インデックスの定義で各フィールドにnull_valueというものを設定すると、ドキュメント登録時当該フィールドがnullならば特定の値でインデックスされる
例えば先程の例のインデックス定義を少し変えた新しいインデックスを以下のように定義する

PUT simple_index_with_null_value
{
    "mappings":
    {
        "_routing": {"required": false},
        "properties":
        {
            "id": {"type": "keyword"},
            "name": {"type": "keyword", "null_value": "NULL"},
            "int_value": {"type": "integer", "null_value": -10}
        }
    }
}

上記ではnameとint_valueそれぞれに"null_value"という項目を以下の通り設定している

  • name: このフィールドがnullで登録された場合"NULL"としてインデックスする
  • int_value: このフィールドがnullで登録された場合-10としてインデックスする

例えばnameに対するNULLの検索は「nameがNULLという文字列である」といったやり方で検索できる。つまり

GET simple_index_with_null_value/_search
{
  "size": 10,
  "query": {
    "term": {
      "name": "NULL"
    }
  }
}

といった形で検索できる。
このとき検索結果は以下のようになっている。

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "simple_index_with_null_value",
        "_type" : "_doc",
        "_id" : "iikWM3gBWn03-3bcybO_",
        "_score" : 1.0,
        "_source" : {
          "id" : "1",
          "name" : "sample_1",
          "int_value" : 10
        }
      },
      {
        "_index" : "simple_index_with_null_value",
        "_type" : "_doc",
        "_id" : "jCkWM3gBWn03-3bcybO_",
        "_score" : 1.0,
        "_source" : {
          "id" : "3",
          "name" : "sample_3",
          "int_value" : null
        }
      }
    ]
  }
}

あくまでインデックスされるときにNULLとしてインデックスされるだけで、_sourceの中身が変わるわけではない点に注意1

数値型のフィールドに対しても同様にnull_valueを設定できるが、null_valueの設定方法によっては予期せぬ検索結果を返してしまうことがある
↑の例ではnull_valueを-10にしているが、以下のようなクエリの場合本来NULLにしたいはずのid=3のレコードが検索に引っかかってしまう

GET simple_index_with_null_value/_search
{
  "size": 10,
  "query": {
    "bool": 
    {
      "must": [
        {
          "range": {
            "int_value": {
              "lte": 10
            }
          }
        }
      ]
    }
  }
}

検索結果は以下の通り

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "simple_index_with_null_value",
        "_type" : "_doc",
        "_id" : "4HBWMXgBO19uWpKIOI1_",
        "_score" : 1.0,
        "_source" : {
          "id" : "1",
          "name" : "sample_1",
          "int_value" : 10
        }
      },
      {
        "_index" : "simple_index_with_null_value",
        "_type" : "_doc",
        "_id" : "4nBWMXgBO19uWpKIOI1_",
        "_score" : 1.0,
        "_source" : {
          "id" : "3",
          "name" : "sample_3",
          "int_value" : null
        }
      }
    ]
  }
}

文字列型の場合はある程度このままでも使えそうだが、数値型の場合この方法だと思わぬ挙動になってしまうのであまりよろしくない
したがって別の方法を考える

existsを使う

existsクエリ自体は「あるフィールドにインデックスされた値2を含むドキュメントを返す」というもの
例えば先のsimple_indexの場合、int_valueがNULLでないドキュメントを取得するクエリは以下のようになる

GET simple_index/_search
{
  "query": {
    "bool": {
        "must": {
            "exists": {
              "field": "int_value"
            }   
        }
    }
  }
}

このexistsとmust_notを組み合わせることで「あるフィールドがNULLであるようなドキュメント」を検索することができる
例えば以下のようなクエリ

GET simple_index/_search
{
  "query": {
    "bool": {
        "must_not": {
            "exists": {
              "field": "int_value"
            }   
        }
    }
  }
}

検索結果

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "simple_index",
        "_type" : "_doc",
        "_id" : "X3BRMXgBO19uWpKIwo1N",
        "_score" : 0.0,
        "_source" : {
          "id" : "3",
          "name" : "sample_3",
          "int_value" : null
        }
      }
    ]
  }
}

ここで注意として、インデックスの定義でnull_valueを設定している場合、null_valueの設定されたフィールドがnullであるドキュメントが上記のクエリで引っかからなくなるということが挙げられる
例えば先程null_valueを設定したsimple_index_with_null_valueで同様のクエリを投げると検索結果が変わる

GET simple_index_with_null_value/_search
{
  "query": {
    "bool": {
        "must_not": {
            "exists": {
              "field": "int_value"
            }   
        }
    }
  }
}

検索結果

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

（個人的な興味）nestedで使うにはどうする？

個人的にネストされた項目に対する検索をすることが多いので、nested内でも同じことができるか確認
結論、nestedでも問題なく使える

以下のようなインデックス定義を考える

PUT nested_index
{
    "mappings": 
    {
        "_routing": {"required": false},
        "properties": 
        {
            "parent_id": {"type": "keyword"},
            "parent_name": {"type": "keyword"},
            "child_info": {
                "type": "nested",
                "properties": {
                    "child_id": {"type": "keyword"},
                    "child_name": {"type": "keyword"},
                    "seq_no": {"type": "integer"},
                    "int_value": {"type": "integer"}
                }
            }
        }
    }
}

データ投入

{"create":{"_index": "nested_index"}}
{ "parent_id": "1","parent_name": "sample_1","child_info": [{ "child_id": "11","child_name": "sample_11", "seq_no": 1,"int_value": 10},{ "child_id": "11","child_name": "sample_11", "seq_no": 2,"int_value": 10}]}
{"create":{"_index": "nested_index"}}
{ "parent_id": "2","parent_name": null, "child_info": [{ "child_id": "12","child_name": "sample_12", "seq_no": 1,"int_value": 20},{ "child_id": "22","child_name": "sample_22", "seq_no": 2,"int_value": 10}]}
{"create":{"_index": "nested_index"}}
{ "parent_id": "3","parent_name": "sample_3","child_info": [{ "child_id": "13","child_name": "sample_13", "seq_no": 1,"int_value": null},{ "child_id": "23","child_name": "sample_23", "seq_no": 2,"int_value": 10}]}
{"create":{"_index": "nested_index"}}
{ "parent_id": "4","parent_name": "sample_4","child_info": [{ "child_id": "14","child_name": "sample_14", "seq_no": 1,"int_value": 40},{ "child_id": "24","child_name": "sample_24", "seq_no": 2,"int_value": 10}]}
{"create":{"_index": "nested_index"}}
{ "parent_id": "5","parent_name": "sample_5","child_info": [{ "child_id": "15","child_name": "sample_15", "seq_no": 1,"int_value": 50},{ "child_id": "25","child_name": "sample_25", "seq_no": 2,"int_value": 10}]}
{"create":{"_index": "nested_index"}}
{ "parent_id": "6","parent_name": "sample_6","child_info": [{ "child_id": "16","child_name": "sample_16", "seq_no": 1,"int_value": null},{ "child_id": "26","child_name": "sample_26", "seq_no": 2,"int_value": 10}]}```

単純な検索
ここではchild_info.int_valueがNULLであるフィールドを含むドキュメントを検索

GET nested_index/_search
{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "path": "child_info",
                        "query": {
                            "bool": {
                                "must_not": {
                                    "exists": {
                                        "field": "child_info.int_value"
                                    }
                                }
                            }
                        },
                        "inner_hits": {}
                    }
                }
            ]
        }
    }
}

検索結果

{
  "took" : 4,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "nested_index",
        "_type" : "_doc",
        "_id" : "3XBzMXgBO19uWpKIrZDn",
        "_score" : 0.0,
        "_source" : {
          "parent_id" : "3",
          "parent_name" : "sample_3",
          "child_info" : [
            {
              "child_id" : "13",
              "child_name" : "sample_13",
              "seq_no" : 1,
              "int_value" : null
            },
            {
              "child_id" : "23",
              "child_name" : "sample_23",
              "seq_no" : 2,
              "int_value" : 10
            }
          ]
        },
        "inner_hits" : {
          "child_info" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 0.0,
              "hits" : [
                {
                  "_index" : "nested_index",
                  "_type" : "_doc",
                  "_id" : "3XBzMXgBO19uWpKIrZDn",
                  "_nested" : {
                    "field" : "child_info",
                    "offset" : 0
                  },
                  "_score" : 0.0,
                  "_source" : {
                    "child_id" : "13",
                    "child_name" : "sample_13",
                    "seq_no" : 1,
                    "int_value" : null
                  }
                }
              ]
            }
          }
        }
      },
      {
        "_index" : "nested_index",
        "_type" : "_doc",
        "_id" : "4HBzMXgBO19uWpKIrZDn",
        "_score" : 0.0,
        "_source" : {
          "parent_id" : "6",
          "parent_name" : "sample_6",
          "child_info" : [
            {
              "child_id" : "16",
              "child_name" : "sample_16",
              "seq_no" : 1,
              "int_value" : null
            },
            {
              "child_id" : "26",
              "child_name" : "sample_26",
              "seq_no" : 2,
              "int_value" : 10
            }
          ]
        },
        "inner_hits" : {
          "child_info" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 0.0,
              "hits" : [
                {
                  "_index" : "nested_index",
                  "_type" : "_doc",
                  "_id" : "4HBzMXgBO19uWpKIrZDn",
                  "_nested" : {
                    "field" : "child_info",
                    "offset" : 0
                  },
                  "_score" : 0.0,
                  "_source" : {
                    "child_id" : "16",
                    "child_name" : "sample_16",
                    "seq_no" : 1,
                    "int_value" : null
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

ネストされた内容に対する複合的な検索
ここでは「child_infoのint_valueがNULLで、かつchild_infoのchild_nameがsample_13」であるようなドキュメントを検索している

GET nested_index/_search
{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "path": "child_info",
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                        "match": {
                                            "child_info.child_name": "sample_13"
                                        }
                                    }
                                ],
                                    "must_not": [
                                      {
                                        "exists": {
                                          "field": "child_info.int_value"
                                        }
                                      }
                                    ]
                            }
                        },
                        "inner_hits": {}
                    }
                }
            ]
        }
    }
}

検索結果

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 2.1594841,
    "hits" : [
      {
        "_index" : "nested_index",
        "_type" : "_doc",
        "_id" : "3XBzMXgBO19uWpKIrZDn",
        "_score" : 2.1594841,
        "_source" : {
          "parent_id" : "3",
          "parent_name" : "sample_3",
          "child_info" : [
            {
              "child_id" : "13",
              "child_name" : "sample_13",
              "seq_no" : 1,
              "int_value" : null
            },
            {
              "child_id" : "23",
              "child_name" : "sample_23",
              "seq_no" : 2,
              "int_value" : 10
            }
          ]
        },
        "inner_hits" : {
          "child_info" : {
            "hits" : {
              "total" : {
                "value" : 1,
                "relation" : "eq"
              },
              "max_score" : 2.1594841,
              "hits" : [
                {
                  "_index" : "nested_index",
                  "_type" : "_doc",
                  "_id" : "3XBzMXgBO19uWpKIrZDn",
                  "_nested" : {
                    "field" : "child_info",
                    "offset" : 0
                  },
                  "_score" : 2.1594841,
                  "_source" : {
                    "child_id" : "13",
                    "child_name" : "sample_13",
                    "seq_no" : 1,
                    "int_value" : null
                  }
                }
              ]
            }
          }
        }
      }
    ]
  }
}

考慮できていないこと

existsとmust_notを使った場合のパフォーマンスについては未検証
missingと比べたら早いというやり取りは見たが、実際のデータを投入したときにどうなるか検証する必要がありそう

以上

参考資料

Exists query - Elasticsearch Reference [7.x]
Boolean query - Elasticsearch Reference [7.x]
Need to get the field having only null value using query

  1. The null_value only influences how data is indexed, it doesn’t modify the _source documentとの注釈こちらにされている ↩

  2. Exists query - Elasticsearch Reference [7.x]にあるフィールドの値がインデックスされない理由が記載されているが、ここでは省略 ↩

5
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
4