Hey，大家好！我是 Elasticsearch 新手。我想知道如何通过 Query DSL 找出指定字段值为 null 的文档。

以下是我索引（索引名：class_info_v22002，用来存放班级信息）创建的 DSL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
PUT class_info_v22002
{
  "settings": {
    "refresh_interval": "1s",
    "number_of_replicas": 1,
    "number_of_shards": 3
  }, 
  "mappings": {
    "properties": {
      "class_id": {
        "type": "integer"
      },
      "class_name": {
        "type": "keyword"
      },
      "teacher": {
        "type": "keyword"
      }
    }
  }
}

索引的文档内容类似如下的示例数据（通过批量添加数据模拟）：

1
2
3
4
5
6
7
8
9
PUT class_info_v22002/_bulk
{"index": {"_id": "1"}}
{"class_id": 2008001,"class_name": "三年（1）班","teacher": "张三"}
{"index": {"_id": "2"}}
{"class_id": 2008002,"class_name": "三年（2）班","teacher": "李四"}
{"index": {"_id": "3"}}
{"class_id": 2008003,"class_name": "三年（3）班","teacher": "王五"}
{"index": {"_id": "4"}}
{"class_id": 2008004,"class_name": "三年（4）班","teacher": null}

如果要找出 teacher 字段值为 null 的文档，Query DSL 应该如何写？

你好，你可以使用以下的 DSL 筛选出 teacher 字段值为 null 的文档：

1
2
3
4
5
6
7
8
9
10
11
12
GET class_info_v22002/_search
{
  "query": {
    "bool": {
      "must_not": {
        "exists": {
          "field": "teacher"
        }
      }
    }
  }
}

返回结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "class_info_v22002",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : 0.0,
        "_source" : {
          "class_id" : 2008004,
          "class_name" : "三年（4）班",
          "teacher" : null
        }
      }
    ]
  }
}

你也可以使用以下 SQL 来实现：

1
2
3
4
POST _sql?format=txt
{
  "query": "select * from class_info_v22002 where teacher is null"
}

返回结果：

1
2
3
   class_id    |  class_name   |    teacher    
---------------+---------------+---------------
2008004        |三年（4）班     |null

（END）