ES查询示例

环境：ES7.4+Kiban7.4.1

一、查询无关项

（1）别名 aliases

#aliases别名
PUT my_index
{"settings": {"number_of_shards": 5,"number_of_replicas": 1},"mappings": {"properties": {"field1":{"type": "text"},"field2":{"type": "text"}}},"aliases": {"default_index": {}}
}

以下语句得到的结果相同

GET my_index/_doc/1
GET default_index/_doc/1

（2）更改_settings信息

#查询分片信息
GET my_index/_settings#修改分片信息
PUT my_index/_settings
{"index":{"number_of_replicas": 1}
}

（3）查看_mappings信息

#查询mappings
GET my_index/_mappings

修改的话如果有了数据，就会报各种BUG，所以最好在没有数据之前就把mappings准备好

（4）几种删除索引方法

#删除索引my_index
DELETE my_index#删除所有以my_ingdex开头的索引
DELETE /my_index*#rm -rf, ^_^,您可以亲自试一下，但是不要在生产环境
DELETE /_all

二、查询相关项

2.1 准备工作

（1）新建book索引，使用IK分词

DELETE book
PUT /book/
{"settings": {"number_of_shards": 1,"number_of_replicas": 0},"mappings": {"properties": {"name":{"type": "text","analyzer": "ik_max_word","search_analyzer": "ik_smart"},"description":{"type": "text","analyzer": "ik_max_word","search_analyzer":"ik_smart"},"studymodel":{"type": "keyword"},"price":{"type": "double"},"timestamp": {"type": "date","format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"},"pic":{"type":"text","index":false}}}
}

（2）给book插入数据

PUT /book/_doc/1
{"name": "Bootstrap开发","description": "Bootstrap是由Twitter推出的一个前台页面开发css框架，是一个非常流行的开发框架，此框架集成了多种页面效果。此开发框架包含了大量的CSS、JS程序代码，可以帮助开发者（尤其是不擅长css页面开发的程序人员）轻松的实现一个css，不受浏览器限制的精美界面css效果。","studymodel": "201002","price":38.6,"timestamp":"2020-05-25 19:11:35","pic":"group1/M00/00/00/wKhlQFs6RCeAY0pHAAJx5ZjNDEM428.jpg","tags": [ "bootstrap", "dev"]
}PUT /book/_doc/2
{"name": "java编程思想","description": "java语言是世界第一编程语言，在软件开发领域使用人数最多。","studymodel": "201001","price":68.6,"timestamp":"2020-05-25 19:11:35","pic":"group1/M00/00/00/wKhlQFs6RCeAY0pHAAJx5ZjNDEM428.jpg","tags": [ "java", "dev"]
}PUT /book/_doc/3
{"name": "spring开发基础","description": "spring 在java领域非常流行，java程序员都在用。","studymodel": "201001","price":88.6,"timestamp":"2020-05-25 19:11:35","pic":"group1/M00/00/00/wKhlQFs6RCeAY0pHAAJx5ZjNDEM428.jpg","tags": [ "spring", "java"]
}PUT /book/_doc/4
{"description": "spring 在java领域非常流行，java程序员都在用。","studymodel": "201001","price":88.6,"timestamp":"2020-05-25 19:11:35","pic":"group1/M00/00/00/wKhlQFs6RCeAY0pHAAJx5ZjNDEM428.jpg","tags": [ "spring", "java"]
}

2.2 QueryString查询方式

#max_score" : 1.0 相关匹配度

#查看book
GET book#查询所有数据
GET book/_search#根据条件查询
GET book/_search?q=name:java#添加排序条件 sort
GET book/_search?q=name:java&sort=price:desc#添加timeout,数据量过大时保住业务
GET book/_search?q=name:java&sort=price:desc&timeout=10ms#多索引搜索
GET /blog,book/_search?q=name:spring#查询以book开头的索引数据
GET /book*/_search#分页搜索
GET book/_search?sort=id:asc&from=1&size=2#包含与不包含
#查询name包含java的数据
GET book/_search?q=+name:java#查询name不包含java的数据
GET book/_search?q=-name:java

2.3 QueryDSL查询方式

#query DSL
#查询所有
GET book/_search
{"query": {"match_all": {}}
}#match，相当于=
GET /book/_search
{"query": {"match": {"description": "java程序员"}}
}#匹配条件并且排序
GET book/_search
{"query" : {"match" : {"name" : " java"}},"sort": [{ "price": "desc" }]
}#分页查询from开始行，size总数
GET  /book/_search 
{"query": { "match_all": {} },"from": 0,"size": 1
}#指定返回某些字段
#这里只返回name与description字段
GET book/_search
{"query": {"match_all": {}},"_source": ["name","description"]
}#在多个属性中查询
#这里从name和description中查询包含java程序员的索引
GET /book/_search
{"query": {"multi_match": {"query": "java程序员","fields": ["name", "description"]}}
}#范围查询
GET /book/_search
{"query": {"range": {"price": {"gte": 80,"lte": 90}}}
}#term，这里使用后ik分词就不起作用了
GET /book/_search
{"query": {"term": {"description": "java程序员"}}
}#查询出包含的项
#查询tags同时包含spring或nosql字段的索引
GET /book/_search
{"query": {"terms": {"tags": ["spring", "nosql"]}}
}#查询是否包含某些key
#用来验证key是否存在
GET /_search
{"query": {"exists": {"field": "name"}}
}#Fuzzy 类似查询，相关查询，用户即使输错了，一样可以搜索到想要的结果
GET book/_search
{"query": {"fuzzy": {"description": {"value": "程序猿"}}}
}#ids 根据id查询
GET /book/_search
{"query": {"ids" : {"values" : ["1", "4", "100"]}}
}#根据前缀查询
GET /book/_search
{"query": {"prefix": {"description": {"value": "spring"}}}
}#regexp query  正则查询
GET /book/_search
{"query": {"regexp": {"description": {"value": "j.*a","flags" : "ALL","max_determinized_states": 10000,"rewrite": "constant_score"}}}
}

2.4 Filter

举个栗子！

查询description中有"java程序员"，并且价格大于80小于90的数据。

GET /book/_search
{"query": {"bool": {"must": [{"match": {"description": "java程序员"}},{"range": {"price": {"gte": 80,"lte": 90}}}]}}
}

使用Filter

#使用filter
GET /book/_search
{"query": {"bool": {"must": [{"match": {"description": "java程序员"}}],"filter": {"range": {"price": {"gte": 80,"lte": 90}}}}}
}

因为学了太久了，忘记了这个filter有啥用了

不过我猜是查的更快，不然干嘛要用呢！

2.5 定位错误语法位置

（1）先写一个错误的语法试试

#定位错误语法
GET /book/_validate/query?explain
{"query": {"mach": {"description": "java程序猿"}}
}

输出信息：

{"valid" : false,"error" : "org.elasticsearch.common.ParsingException: no [query] registered for [mach]"
}

好的，现在告诉了我们，match写错了，写成mach了。

（2）在写一个对的语句试试

GET /book/_validate/query?explain
{"query": {"match": {"description": "java程序猿"}}
}

输出信息：

{"_shards" : {"total" : 1,"successful" : 1,"failed" : 0},"valid" : true,"explanations" : [{"index" : "book","valid" : true,"explanation" : "description:java description:程序 description:猿"}]
}

当搜索量很大时，先进行验证语法正确性在查，应该可以省掉很多无意义的消耗！

2.6 滚动查询

（1）第一条语句

#Scroll =1m就是1分钟之内查出来就行
GET /book/_search?scroll=1m
{"query": {"match_all": {}},"size": 3
}

输出结果会有一个_scroll_id

{"_scroll_id" : "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAHTgWTTlUQUJIblVSU085Y3dJbTJXV3lGUQ==",……………………………………
}

（2）之后的语句

GET /_search/scroll
{"scroll":"1m","scroll_id": "DXF1ZXJ5QW5kRmV0Y2gBAAAAAAAAHTgWTTlUQUJIblVSU085Y3dJbTJXV3lGUQ=="
}

恭喜你学会了滚动查询！

2.7 聚合查询

（1）计算每个studymodel下的商品数量

GET /book/_search
{"size": 0,"query": {"match_all": {}},"aggs": {"group_by_model": {"terms": {"field": "studymodel","size": 10}}}
}

（2）计算每个tags下的商品数目

PUT /book/_mapping/
{"properties": {"tags": {"type": "text","fielddata": true}}
}GET /book/_search
{"size": 0, "query": {"match_all": {}},"aggs": {"group_by_tags": {"terms": {"field": "tags","size": 10}}}
}

（3）加上搜索条件，计算每个tags下的商品数量

GET /book/_search
{"size": 0, "query": {"match": {"description": "java程序员"}},"aggs": {"group_by_tags" : {"terms": {"field": "tags"}}}
}

（4）先分组，再算每组的平均值，计算每个tag下的商品的平均价格

GET /book/_search
{"size": 0,"aggs": {"group_by_tags": {"terms": {"field": "tags"},"aggs": {"avg_price": {"avg": {"field": "price"}}}}}
}

（5）计算每个tag下的商品的平均价格，并且按照平均价格降序排序

GET /book/_search
{"size": 0,"aggs": {"group_by_tags": {"terms": {"field": "tags","order": {"avg_price": "asc"}},"aggs": {"avg_price": {"avg": {"field": "price"}}}}}
}

（6）按照指定的价格范围区间进行分组，然后在每组内再按照tag进行分组，最后再计算每组的平均价格

GET /book/_search
{"size": 0,"aggs": {"group_by_price": {"range": {"field": "price","ranges": [{"from": 0,"to": 40},{"from": 40,"to": 60},{"from": 60,"to": 80}]},"aggs": {"group_by_tags": {"terms": {"field": "tags","order": {"avg_price": "asc"}},"aggs": {"avg_price": {"avg": {"field": "price"}}}}}}}
}

2.8 聚合实例2

2.8.1准备数据

PUT /tvs/
{"settings": {"number_of_shards": 1,"number_of_replicas": 1},"mappings": {"properties": {"price":{"type": "long"},"color":{"type": "keyword"},"brand":{"type": "keyword"},"sold_date": {"type": "date","format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"}}}
}GET /tvs#bulk放入数据
POST /tvs/_bulk
{ "index": {}}
{ "price" : 1000, "color" : "红色", "brand" : "长虹", "sold_date" : "2019-10-28" }
{ "index": {}}
{ "price" : 2000, "color" : "红色", "brand" : "长虹", "sold_date" : "2019-11-05" }
{ "index": {}}
{ "price" : 3000, "color" : "绿色", "brand" : "小米", "sold_date" : "2019-05-18" }
{ "index": {}}
{ "price" : 1500, "color" : "蓝色", "brand" : "TCL", "sold_date" : "2019-07-02" }
{ "index": {}}
{ "price" : 1200, "color" : "绿色", "brand" : "TCL", "sold_date" : "2019-08-19" }
{ "index": {}}
{ "price" : 2000, "color" : "红色", "brand" : "长虹", "sold_date" : "2019-11-05" }
{ "index": {}}
{ "price" : 8000, "color" : "红色", "brand" : "三星", "sold_date" : "2020-01-01" }
{ "index": {}}
{ "price" : 2500, "color" : "蓝色", "brand" : "小米", "sold_date" : "2020-02-12" }

2.8.2 查询

（1）需求1 统计哪种颜色的电视销量最高

GET /tvs/_search
{"query": {"match_all": {}},"size": 0,"aggs": {"popular_colors": {"terms": {"field": "color"}}}
}

（2）需求2统计每种颜色电视的平均价格

GET /tvs/_search?size=0
{"aggs": {"popular_colors": {"terms": {"field": "color"},"aggs": {"avg_color_price": {"avg": {"field": "price"}}}}}
}

（3）#需求3 继续下钻分析，每个颜色下，平均价格及每个颜色下，每个品牌的平均价格

GET /tvs/_search?size=0
{"aggs": {"popular_colors": {"terms": {"field": "color"},"aggs": {"avg_color_price": {"avg": {"field": "price"}},"group_by_brand": {"terms": {"field": "brand"},"aggs": {"avg_brand_price": {"avg": {"field": "price"}}}}}}}
}

（4）需求4 更多的metric

GET /tvs/_search?size=0
{"aggs": {"group_by_color": {"terms": {"field": "color"},"aggs": {"avg_price": {"avg": {"field": "price"}},"max_price":{"max": {"field": "price"}},"min_price":{"min": {"field": "price"}},"sum_price":{"sum": {"field": "price"}}}}}
}

（5）需求5：划分范围 histogram

GET /tvs/_search?size=0
{"aggs": {"price_qujian": {"histogram": {"field": "price","interval": 1000},"aggs": {"money": {"sum": {"field": "price"}}}}}
}

（6）需求6：按照日期分组聚合

GET /tvs/_search?size=0
{"aggs": {"date_sales": {"date_histogram": {"field": "sold_date","calendar_interval": "month","format": "yyyy-MM-dd","min_doc_count": 0,"extended_bounds": {"min": "2019-01-01","max": "now/d"}}}}
}

（7）需求7 统计每季度每个品牌的销售额

GET /tvs/_search?size=0
{"aggs": {"group_by_sold_date": {"date_histogram": {"field": "sold_date","calendar_interval": "quarter","format": "yyyy-MM-dd","min_doc_count": 0,"extended_bounds": {"min": "2019-01-01","max": "now/d"}},"aggs": {"group_by_brand": {"terms": {"field": "brand"},"aggs": {"sum_price": {"sum": {"field": "price"}}}},"total_sum_price":{"sum": {"field": "price"}}}}}
}

（8）搜索与聚合结合，查询某个品牌按颜色销量

GET /tvs/_search?size=0
{"query": {"term": {"brand": {"value": "小米"}}},"aggs": {"group_by_color": {"terms": {"field": "color"}}}
}

（9）需求9 global bucket：单个品牌与所有品牌销量对比

GET /tvs/_search 
{"size": 0, "query": {"term": {"brand": {"value": "小米"}}},"aggs": {"single_brand_avg_price": {"avg": {"field": "price"}},"all": {"global": {},"aggs": {"all_brand_avg_price": {"avg": {"field": "price"}}}}}
}

（10）需求10：过滤+聚合：统计价格大于1200的电视平均价格

GET /tvs/_search 
{"size": 0,"query": {"constant_score": {"filter": {"range": {"price": {"gte": 1200}}}}},"aggs": {"avg_price": {"avg": {"field": "price"}}}
}

（11）需求11 bucket filter：统计品牌最近一个月的平均价格

GET /tvs/_search 
{"size": 0,"query": {"term": {"brand": {"value": "小米"}}},"aggs": {"recent_150d": {"filter": {"range": {"sold_date": {"gte": "now-150d"}}},"aggs": {"recent_150d_avg_price": {"avg": {"field": "price"}}}},"recent_140d": {"filter": {"range": {"sold_date": {"gte": "now-140d"}}},"aggs": {"recent_140d_avg_price": {"avg": {"field": "price"}}}},"recent_130d": {"filter": {"range": {"sold_date": {"gte": "now-130d"}}},"aggs": {"recent_130d_avg_price": {"avg": {"field": "price"}}}}}
}

（12）需求12 排序：按每种颜色的平均销售额降序排序

GET /tvs/_search 
{"size": 0,"aggs": {"group_by_color": {"terms": {"field": "color","order": {"avg_price": "desc"}},"aggs": {"avg_price": {"avg": {"field": "price"}}}}}
}

（13）需求13排序：按每种颜色的每种品牌平均销售额降序排序

GET /tvs/_search  
{"size": 0,"aggs": {"group_by_color": {"terms": {"field": "color"},"aggs": {"group_by_brand": {"terms": {"field": "brand","order": {"avg_price": "desc"}},"aggs": {"avg_price": {"avg": {"field": "price"}}}}}}}
}

2.9 ES7sql

es7.x的新特性，商业版本可以直接使用SQL进行查询！然并卵！

#快速入门
POST /_sql?format=txt
{"query":"select color,avg(price),max(price),min(price),sum(price) from tvs group by color "
}#显示形式
POST /_sql?format=tsv
{"query":"select color,avg(price),max(price),min(price),sum(price) from tvs group by color "
}#sql 翻译
POST /_sql/translate
{"query": "SELECT * FROM tvs "
}#搭配其他DSL使用
POST /_sql?format=tsv
{"query": "SELECT * FROM tvs ","filter":{"range": {"price": {"gte" : 1200,"lte" : 2000}}}
}