es语法
创建索引(指定settings + mappings)：
创建一个2个副本3个分片的索引，名字是user，有以下字段：
integer类型的age，keyword类型的name，keyword类型的gender，text类型的desc，date类型的birthday(格式要求型如2026-04-28 12:30:00)，boolean类型的is_deleted
PUT user
{
"settings": {
"number_of_shards": 3,
"number_of_replicas": 2
},
"mappings": {
"properties": {
"age": {
"type": "integer"
},
"name": {
"type": "keyword"
},
"gender": {
"type": "keyword"
},
"desc": {
"type": "text"
},
"birthday": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss"
},
"is_deleted": {
"type": "boolean"
}
}
}
}
如果desc字段即想搜索，又想精确匹配，则
"desc": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword"
}
}
}
插入：
不指定id：
POST /user/_doc
{
"age": 60,
"name": "王大拿",
"gender": "male",
"desc": "象牙山首富",
"birthday": "1966-03-28 00:00:00",
"is_deleted": false
}
指定id：
POST /user/_doc/1
{
"age": 30,
"name": "王小蒙",
"gender": "female",
"desc": "象牙山豆腐厂掌门人",
"birthday": "1996-03-28 00:00:00",
"is_deleted": false
}
批量插入：
POST /_bulk
{"index":{"_index":"user","_id":4,"_version":1}}
{"age":58,"name":"谢广坤","gender":"male","desc":"象牙山事逼","birthday":"1968-03-28 00:00:00","is_deleted":false}
{"index":{"_index":"user","_id":5,"_version":2}}
{"age":32,"name":"谢永强","gender":"male","desc":"王小蒙老公，怂比","birthday":"1994-03-28 00:00:00","is_deleted":false}
删除文档：
按id删：
DELETE /user/_doc/1
按条件删：
POST /user/_delete_by_query
{
"query": {
"term": {
"age": 60
}
}
}
更改：
根据id=xxx更改，要使用_update
update user set age=56,birtdhay='1970-03-28 12:00:00' where id=4
POST /user/_update/4
{
"doc": {
"age": 56,
"birthday": "1970-03-28 12:00:00"
}
}
如果想不存在就插入，则使用doc_as_upsert
{
"doc": {
"age": 56,
"birthday": "1970-03-28 12:00:00"
},
"doc_as_upsert": true
}
根据条件更新，要使用_update_by_query
update user set desc='象牙山一代目', is_deleted=true where age > 55
POST /user/_update_by_query
{
"query": {
"range": {
"age": {
"gt": 55
}
}
},
"script": {
"source": "ctx._source.desc=params.desc; ctx._source.is_deleted=params.is_deleted",
"params": {
"desc": "象牙山一代目",
"is_deleted": true
}
}
}
查询：
sql查询：
POST _sql?format=txt
{"query":"select * from user where gender = 'male'"}
非sql查询：
GET /user/_search
select * from user where age=18，用term query
{
"query": {
"term": {
"age": 18
}
}
}
select * from user where age in (16,17,18)，用terms query
{
"query": {
"terms": {
"age": [
16,
17,
18
]
}
}
}
select * from user where age>16 and age<18，用range query
{
"query": {
"range": {
"age": {
"gt": 16,
"lt": 18
}
}
}
}
select * from user where age!=18，用bool.must_not
{
"query": {
"bool": {
"must_not": {
"term": 18
}
}
}
}
select * from user where age not in (16,17)
{
"query": {
"bool": {
"must_not": {
"terms": {
"age": [
16,
17
]
}
}
}
}
}
select * from user where is_deleted=false and age=18，用bool.filter或者bool.must，前者不会计算score，会更快更省资源。filter是个json数组。
{
"query": {
"bool": {
"filter": [
{
"term": {
"is_deleted": false
}
},
{
"term": {
"age": 1
}
}
]
}
}
}
select * from user where is_deleted=false and age>16 and age<18
{
"query": {
"bool": {
"filter": [
{
"term": {
"is_deleted": false
}
},
{
"range": {
"age": {
"gt": 16,
"lt": 18
}
}
}
]
}
}
}
select * from user where age=18 and (is_deleted=false or gender='male')
and用bool.filter，or用bool.should，条件a or 条件b or 条件c，必须指定minimum_should_match为1。
{
"query": {
"bool": {
"filter": [
{
"term": {
"age": 18
}
},
{
"bool": {
"should": [
{
"term": {
"is_deleted": false
}
},
{
"term": {
"gender": male
}
}
],
"minimum_should_match": 1
}
}
]
}
}
}
bool对应的json结构体可以有4个属性，must、filter、should、must_not。must是必须满足，打分，等价于and。filter是必须满足，不打分，等价于and。should是可选，等价于or。must_not是必须不满足，等价于not。filter 是“要满足的条件集合”，must_not 是“要排除的条件集合”，两者是并列关系。
select * from user where A and B and not C
对应
{
"bool": {
"filter": [A, B],
"must_not": [C]
}
}
select * from user where name like '%张三%'，模糊查询分词字段，用match
{
"query": {
"match": {
"name": "张三"
}
}
}
select * from user order by age desc，用sort。sort和query关键字是同级的。因为可能有多个排序字段，所以sort是个数组
{
"sort": [
{
"age": "desc"
}
]
}
limit 0,10，用from、size，from、size和query关键字是同级的。
{
"from": 0,
"size": 10
}
select * from user where age = 18 and name in ('谢广坤','谢永强') and (is_deleted = false or gender = 'male') order by age desc limit 0,10
{
"query": {
"bool": {
"filter": [
{
"term": {
"age": 18
}
},
{
"terms": {
"name": [
'谢广坤',
'谢永强'
]
}
},
{
"bool": {
"should": [
{
"term": {
"is_deleted": false
}
},
{
"term": {
"gender": 'male'
}
}
],
"minimum_should_match": 1
}
}
]
}
},
"sort": [
{
"age": "desc"
}
],
"from": 0,
"size": 10
}
select * from user where id not in (1,2,5) and age=18 and name in ('谢广坤','谢永强') and (is_deleted = false or gender = 'male') order by age desc limit 0,10
{
"query": {
"bool": {
"must_not": {
"terms": {
"id": [
1,
2,
5
]
}
},
"filter": [
{
"term": {
"age": 18
}
},
{
"terms": {
"status": [
0,
1
]
}
},
{
"bool": {
"should": [
{
"term": {
"deleted": 0
}
},
{
"term": {
"vip": 1
}
}
],
"minimum_should_match": 1
}
}
]
}
},
"sort": [
{
"age": "desc"
}
],
"from": 0,
"size": 10
}
聚合语法：
select gender, count(1) from user group by gender
用aggs关键字，aggs与query同级。size=0表示不返回文档，只返回统计结果，否则会返回所有参与聚合的文档。
GET /user/_search
{
"aggs": {
"group_by_gender": {
"terms": {
"field": "gender"
}
}
},
"size": 0
}
select gender, count(1) from user where is_deleted=false group by gender，先过滤再聚合
{
"size": 0,
"query": {
"term": {
"is_deleted": false
}
},
"aggs": {
"group_by_gender": {
"terms": {
"field": "gender"
}
}
}
}
select count(name), avg(age), max(age), min(age), sum(age) from xxx
{
"size": 0,
"aggs": {
"count_name": {
"value_count": {
"field": "name"
}
},
"avg_age": {
"avg": {
"field": "age"
}
},
"max_age": {
"max": {
"field": "age"
}
},
"min_age": {
"min": {
"field": "age"
}
},
"sum_age": {
"sum": {
"field": "age"
}
}
}
}
select gender, avg(age) from user group by gender，按性别分组，看各性别的平均年龄
{
"size": 0,
"aggs": {
"group_by_gender": {
"terms": {
"field": "gender"
},
"aggs": {
"avg_age": {
"avg": {
"field": "age"
}
}
}
}
}
}
聚合比较难，可以用SQL Transalate API，让es告诉我们DSL怎么写。
POST /_sql/translate
{
"query": "select gender, age, count(1) from user group by gender, age"
}
拿到DSL后，再去执行即可。
在kibana console中，把光标放在命令后面，按ctrl+/，可以跳转到该命令的官方文档。在文档中，不仅可以看到该命令的语法，还能看到客户端api的使用。
浙公网安备 33010602011771号