ES搜索查询笔记

本文不太适合0基础的小白查看。
ES搜索关键字的解释，放在了文章的最后。

SQL语句转换ES搜索语句

示例一

先分析SQL语句：

SELECT * FROM `riskgrade` 
	WHERE 
		( tenantid = 'global' AND dr = 0 ) 
	or 
		( tenantid = 'X813a66W' AND company_id in ('1092295178485')  AND dr = 0 )

对应的ES搜索语句为：

POST　/index索引名/type类型名/_search

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "tenantId.keyword": "global"
                }
              },
              {
                "term": {
                  "dr": 0
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "tenantId.keyword": "X813a66W"
                }
              },
              {
                "term": {
                  "dr": 0
                }
              },
              {
                "terms": {
                  "companyId": [
                    "1092295178485"
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

对应的Java代码

/**
 * 最顶级的命名为 boolTop，也可以称作 bool01，二级命名则为 bool0101、bool0102，
 *      以此类推，三级命名则为 bool010101、bool010102、bool010201、bool010202
 */
BoolQueryBuilder boolTop = QueryBuilders.boolQuery();

List<String> companyIds = cidList;
// sqlWhere第一部分 AND：  ( tenantid = 'global' AND dr = 0 )
BoolQueryBuilder bool0101 = QueryBuilders.boolQuery();
bool0101.must(QueryBuilders.termQuery("tenantId.keyword", "global"));
bool0101.must(QueryBuilders.termQuery("dr", 0));

// sqlWhere第二部分 AND：  ( tenantid = 'X813a66W' AND company_id in ('1092295178485')  AND dr = 0 )
BoolQueryBuilder bool0102 = QueryBuilders.boolQuery();
bool0102.must(QueryBuilders.termQuery("tenantId.keyword", currentTid));
bool0102.must(QueryBuilders.termsQuery("companyId", companyIds));
bool0102.must(QueryBuilders.termQuery("dr", 0));

// sqlWhere OR语句 拼装
boolTop.should(bool0101);
boolTop.should(bool0102);

示例二

SELECT * FROM `riskgrade` 
	WHERE 
			( tenantid = 'global' AND dr = 0 
					AND 
							( grade_name LIKE '%大风险%' OR risk_grade LIKE '%大风险%' OR lecd LIKE '%大风险%' OR ls_combination LIKE '%大风险%')
			)
	OR 
			( tenantid = 'X813a66W' AND company_id in ('1092295178485')  AND dr = 0  
					AND 
							( grade_name LIKE '%大风险%' OR risk_grade LIKE '%大风险%' OR lecd LIKE '%大风险%' OR ls_combination LIKE  '%大风险%')
			)

对应的ES搜索语句为：

POST　/index索引名/type类型名/_search

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "tenantId.keyword": "global"
                }
              },
              {
                "term": {
                  "dr": 0
                }
              },
              {
                "bool": {
                  "should": [
                    {
                      "wildcard": {
                        "gradeName.keyword": "*大风险*"
                      }
                    },
                    {
                      "wildcard": {
                        "riskGrade.keyword": "*大风险*"
                      }
                    },
                    {
                      "wildcard": {
                        "lecd.keyword": "*大风险*"
                      }
                    },
                    {
                      "wildcard": {
                        "lsCombination.keyword": "*大风险*"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "tenantId.keyword": "X813a66W"
                }
              },
              {
                "term": {
                  "dr": 0
                }
              },
              {
                "terms": {
                  "companyId": [
                    "1092295178485"
                  ]
                }
              },
              {
                "bool": {
                  "should": [
                    {
                      "wildcard": {
                        "gradeName.keyword": "*大风险*"
                      }
                    },
                    {
                      "wildcard": {
                        "riskGrade.keyword": "*大风险*"
                      }
                    },
                    {
                      "wildcard": {
                        "lecd.keyword": "*大风险*"
                      }
                    },
                    {
                      "wildcard": {
                        "lsCombination.keyword": "*大风险*"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

对应的Java代码

// like 模糊查询部分
BoolQueryBuilder bool010101 = QueryBuilders.boolQuery();
bool010101.should(QueryBuilders.wildcardQuery("gradeName.keyword","*大风险*"));
bool010101.should(QueryBuilders.wildcardQuery("riskGrade.keyword","*大风险*"));
bool010101.should(QueryBuilders.wildcardQuery("lecd.keyword","*大风险*"));
bool010101.should(QueryBuilders.wildcardQuery("lsCombination.keyword","*大风险*"));

// 顶级 bool
BoolQueryBuilder boolTop = QueryBuilders.boolQuery();
// sqlWhere 第一部分 AND：  ( tenantid = 'global' AND dr = 0 AND ( OR OR OR ) )
BoolQueryBuilder bool0101 = QueryBuilders.boolQuery();
bool0101.must(QueryBuilders.termQuery("tenantId.keyword", "global"));
bool0101.must(QueryBuilders.termQuery("dr", 0));
bool0101.must(bool010101);

// sqlWhere 第二部分 AND：  ( tenantid = 'X813a66W' AND company_id in ('1092295178485')  AND dr = 0 AND (  OR OR OR ) )
BoolQueryBuilder bool0102 = QueryBuilders.boolQuery();
bool0102.must(QueryBuilders.termQuery("tenantId.keyword", currentTid));
bool0102.must(QueryBuilders.termsQuery("companyId", companyIds));
bool0102.must(QueryBuilders.termQuery("dr", 0));
bool0102.must(bool010101);

// sqlWhere OR语句 拼装完整
boolTop.should(bool0101);
boolTop.should(bool0102);

ES搜索引擎的关键字简单解释

对ES搜索引擎的关键字进行简单解释，结合上面的示例，防止遗忘。

• bool：组合查询
• should：数组，等价于 OR
• must：数组，等价于 AND 、=
• must_not：数组，等价于 NOT、!=
• term：精确查询
• match：分词查询