ES语法入门

基本语法

过滤语句

term过滤

等价于sql =

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

select * from tablename where tablefield = "xxxx"

terms过滤

等价于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
   		 ]
   		"minimum_should_match":1
      }
   }
}

minimum_should_match

分数	备注
1	子句满足数量,若无must 与 filter时默认 需要满足 1个should
-1	需要满足 n-1 个should
75%	需要满足75%个should
-75%	需要满足1-75%个should

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

查询语句

match_all 查询

等价全部扫描

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

select * from tablename 

match查询

等价与sql like 以及 contains

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

SELECT * FROM table_name WHERE tablefield like '%a%'

SELECT * FROM table_name WHERE CONTAINS(tablefield,'a')

multi_match查询

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"multi_match":{
   		     "query": "a",
             "fields": ["tablefield1", "tablefield2"]
   		}
   }
}

SELECT * FROM table_name WHERE tablefield1 like '%a%' or tablefield2 like '%a%'

SELECT * FROM table_name WHERE CONTAINS(tablefield1,'a') or CONTAINS(tablefield2,'a')

wildcards查询

等价与sql like

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
   		"wildcards":{
   		    "tablefield":"a?b*"
   		}
   }
}

SELECT * FROM table_name WHERE tablefield like "a_b%"

regexp查询

等价与sql regexp

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{
   "query":{
 		"regexp": { 
           "tablefield": "W[0-9].+" 
       	} 
   }
}

SELECT * FROM table_name WHERE tablefield REGEXP "W[0-9].+"

prefix查询

等价与sql like

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

SELECT * FROM table_name WHERE tablefield like "a%"

bool查询

等价与sql union 或者 or

http://xxx.xxx.xxx.xxx:9200/tablename/_search
{ 
    "bool": { 
        "must":     { "match": { "tablefield1": "a" }}, 
        "must_not": { "match": { "tablefield2": "b" }}, 
        "should": [ 
            { "match": { "tablefield3": "c" }}
        ] 
    } 
}

SELECT * FROM table_name WHERE tablefield1 = "a" and tablefield2 <> "b"
Union 
SELECT * FROM table_name WHERE tablefield3 = "c"

SELECT * FROM table_name WHERE tablefield1 = "a" and tablefield2 <> "b" or tablefield3 = "c"