Elasticsearch 对比 sql

• 基本语法
• • 过滤语句
  • • trem过滤
    • trems过滤
    • range过滤
    • exists过滤
    • missing过滤
    • bool过滤 （组合多个过滤语句）
    • • must
      • must_not
      • should
      • filter

基本语法

过滤语句

trem过滤

等价于sql =

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"term":{
   		  "tablefield":"xxx"
      }
   }
}

select * from tablename where tablefield = "xxxx"

trems过滤

等价于sql in

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"terms":{
   		  "tablefield":["xxx1","xxx2"]
      }
   }
}

select * from tablename where tablefield in ("xxx1","xxx2")

range过滤

等价于 >,<,<=,>=

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"range":{
   		  "tablefield":{
   		     "gte":1,
   		     "le":2,
         }
      }
   }
}
//gt:大于>
//gte:大于等于>=
//lt:小于<
//lte:小于等于<=

select * from tablename where tablefield <2 and tablefield>=1

exists过滤

等价于 is NOT NULL

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"exists":{
   		  "field":"tablefield"
      }
   }
}

select * from tablename where tablefield is NOT NULL

missing过滤

等价于 is NULL

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"missing":{
   		  "field":"tablefield"
      }
   }
}

select * from tablename where tablefield is NULL

bool过滤 （组合多个过滤语句）

must

等价于 and

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"bool":{
   		 "must":[
   		     语句1，
   		     语句2
   		 ]
      }
   }
}

select * from tablename where 语句1 and 语句2

must_not

等价于 and

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"bool":{
   		 "must_not":[
   		     语句1，
   		     语句2
   		 ]
      }
   }
}

select * from tablename where !语句1 and !语句2

should

等价于 or

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"bool":{
   		 "shuld":[
   		     语句1，
   		     语句2
   		 ]
      }
   }
}

select * from tablename where 语句1 or 语句2

filter

等价于 and 与must区别是不参与es算分

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"bool":{
   		 "filter":[
   		     语句1，
   		     语句2
   		 ]
      }
   }
}

select * from tablename where 语句1 and 语句2