问题描述

用mysql语句写下查询示例：select name from table where name in(‘zhangsan’,‘lisi’,‘wangwu’) and name is null
用es语句实现这个查询

实现方式:QueryBuilders.boolQuery().should

QueryBuilders.boolQuery()
    .should(QueryBuilders.termsQuery("name",list))
    .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("name")));

解释：list存放的是name in（‘zhangsan’,‘lisi’,‘wangwu’）；
mustNot(QueryBuilders.existsQuery(“name”)) 语句表示name is null

es原生语句

{
  "from": 0,
  "size": 10,
  "query": {
    "bool": {
      "should": [
        {
          "terms": {
            "name": [
              "zhangsan",
              "lisi",
              "wangwu"
            ]
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "name"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

若多个字段同时查询怎么写

QueryBuilders.boolQuery()
	.must(QueryBuilders.matchQuery("age", age))
	.must(QueryBuilders.boolQuery()
    		.should(QueryBuilders.termsQuery("name",list))
    		.should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("name")));)

解释：用多个boolQuery连接，name条件要用must包住；

es原生语句

{
  "from": 10,
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "age": {
              "query": "18"
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "terms": {
                  "name": [
                    "zhangsan",
             		 "lisi",
             		 "wangwu"
                  ]
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "name"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}