elasticsearch的dsl语法
2022-02-26 本文已影响0人
virtual灬zzZ
基于版本es7.17，kibana7.17。
配置一个该索引下全局分词器
PUT /building
{
"settings": {
"index": {
"analysis.analyzer.default.type": "ik_max_word"
},
"number_of_replicas": 2,
"number_of_shards": 1
},
"mappings": {
"dynamic": false,
"properties": {
"name": {
"type": "text"
},
"shortname": {
"type": "keyword"
},
"create_time": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
},
"history_years": {
"type": "integer"
},
"famous_person": {
"dynamic": false,
"properties": {
"sex": {
"type": "keyword"
},
"age": {
"type": "integer"
},
"p_name": {
"type": "text"
}
}
}
}
}
}
动态映射（dynamic：true）：动态添加新的字段（或缺省）。
静态映射（dynamic：false）：忽略新的字段。在原有的映射基础上，当有新的字段时，不会主动的添加新>的映射关系，只作为查询结果出现在查询中。
严格模式（dynamic： strict）：如果遇到新的字段，就抛出异常
数据模拟
PUT /building/_doc/1
{
"name" : "湖北黄鹤楼",
"shortname":"黄鹤楼",
"create_time":"2000-06-01",
"history_years": 10,
"famous_person":{
"sex":"男",
"age":99,
"p_name":"孟浩然"
}
}
PUT /building/_doc/2
{
"name" : "湖南橘子洲",
"shortname":"橘子洲",
"create_time":"2001-07-01",
"history_years": 11
}
POST /building/_doc/2/_update
{
"doc": {
"famous_person": {
"sex": "男",
"age": 98,
"p_name": "毛泽东"
}
}
}
PUT /building/_doc/3
{
"name" : "湖南岳麓山",
"shortname":"岳麓山",
"create_time":"2003-07-01",
"history_years": 12,
"famous_person":{
"sex":"男",
"age":95,
"p_name":"陈赓"
}
}
PUT /building/_doc/4
{
"name" : "广东白云山",
"shortname":"白云山",
"create_time":"2005-06-01",
"history_years": 17,
"famous_person":{
"sex":"男",
"age":78,
"p_name":"钟南山"
}
}
PUT /building/_doc/5
{
"name" : "广东鼎湖山",
"shortname":"鼎湖山",
"create_time":"2007-06-01",
"history_years": 15,
"famous_person":{
"sex":"男",
"age":92,
"p_name":"余汉谋"
}
}
PUT /building/_doc/6
{
"name" : "广东七星岩",
"shortname":"七星岩",
"create_time":"2008-06-01",
"history_years": 14,
"famous_person":{
"sex":"男",
"age":102,
"p_name":"黎雄才"
}
}
PUT /building/_doc/7
{
"name" : "北京故宫",
"shortname":"故宫",
"create_time":"1992-06-01",
"history_years": 20,
"famous_person":{
"sex":"男",
"age":99,
"p_name":"溥仪"
}
}
PUT /building/_doc/8
{
"name" : "山东趵突泉",
"shortname":"趵突泉",
"create_time":"1999-06-01",
"history_years": 23,
"famous_person":{
"sex":"女",
"age":63,
"p_name":"郎平"
}
}
PUT /building/_doc/9
{
"name" : "四川九寨沟",
"shortname":"九寨沟",
"create_time":"2010-06-01",
"history_years": 12,
"famous_person":{
"sex":"男",
"age":93,
"p_name":"邓小平"
}
}
PUT /building/_doc/10
{
"name" : "四川稻城亚丁",
"shortname":"稻城亚丁",
"create_time":"2015-06-01",
"history_years": 7,
"famous_person":{
"sex":"男",
"age":82,
"p_name":"朱德"
}
}
PUT /building/_doc/11
{
"name" : "江西武功山",
"shortname":"武功山",
"create_time":"2017-06-01",
"history_years": 5,
"famous_person":{
"sex":"男",
"age":54,
"p_name":"黄庭坚"
}
}
PUT /building/_doc/12
{
"name" : "江西滕王阁",
"shortname":"滕王阁",
"create_time":"2018-06-01",
"history_years": 4,
"famous_person":{
"sex":"男",
"age":33,
"p_name":"王勃"
}
}
PUT /building/_doc/13
{
"name" : "UK Ben",
"shortname":"Ben",
"create_time":"2014-08-01",
"history_years": 8,
"famous_person":{
"sex":"男",
"age":77,
"p_name":"Hawking"
}
}
PUT /building/_doc/14
{
"name" : "Italy Milan",
"shortname":"Milan",
"create_time":"2011-07-01",
"history_years": 4,
"famous_person":{
"sex":"男",
"age":33,
"p_name":"Hawking Fake"
}
}
PUT /building/_doc/15
{
"name" : "US Washington",
"shortname":"Washington",
"create_time":"2014-07-01",
"history_years": 3,
"famous_person":{
"sex":"女",
"age":32,
"p_name":"山"
}
}
PUT /building/_doc/16
{
"name" : "Italy Roman",
"shortname":"Roman",
"create_time":"2012-07-04",
"history_years": 4,
"famous_person":{
"sex":"男",
"age":38,
"p_name":"Hawking Fake Dog"
}
}
query查询
GET /building
后面修改为2个备份分片,setting写在body就报错
PUT /building/_settings
{
"number_of_replicas": 2
}
mapping也是写在body就报错
PUT /building/_mapping
{
"dynamic":"strict"
}
GET /building/_doc/_search
使用索引下的分词器进行测试
GET /building/_analyze
{
"text": "山"
}
比对系统默认的分词器进行测试#ik_smart
GET _analyze
{
"analyzer": "ik_max_word",
"text": "广东白云山"
}
精确查询term，对于keyword
GET /building/_doc/_search
{
"query": {
"term": {
"shortname": "白云山"
}
}
}
精确查询term，对于数字
GET /building/_doc/_search
{
"query": {
"term": {
"history_years": "5"
}
}
}
精确查询term，对于日期时间，换一个不存在的日期搜不出
GET /building/_doc/_search
{
"query": {
"term": {
"create_time": "2014-08-01"
}
}
}
模糊查询match,英文大小写不限制，IK分词转化为小写做token
GET /building/_doc/_search
{
"query": {
"match": {
"name": "itAly"
}
}
}
模糊查询match，结果没有出来4-白云山，其余都能查出来，使用分词器ik_max_word验证，并没有分出“山”这个token
GET /building/_doc/_search
{
"query": {
"match": {
"name": "山"
}
}
}
模糊查询match，使用filter不要评分，_score结果是相对耗时的
GET /building/_doc/_search
{
"query": {
"bool": {
"filter": {
"match": {
"name": "山"
}
}
}
}
}
模糊查询match，使用constant_score固定评分为1.0，_score结果是相对耗时的
GET /building/_doc/_search
{
"query": {
"constant_score": {
"filter": {
"match": {
"name": "山"
}
}
}
}
}
精确查询terms，多个值，数字
GET /building/_doc/_search
{
"query": {
"terms": {
"history_years": [4,8]
}
}
}
精确查询terms，多个值，字符串
GET /building/_doc/_search
{
"query": {
"terms": {
"shortname": ["黄鹤楼","岳麓山"]
}
}
}
模糊查询match，多个值，字符串，这是非法的
GET /building/_doc/_search
{
"query": {
"match": {
"name": ["黄鹤楼","岳麓山"]
}
}
}
组合bool查询,bool中有must、must_not、should（or的意思）
GET /building/_doc/_search
{
"query": {
"bool": {
"should": [
{
"match": {
"name": "广东"
}
},
{
"term": {
"shortname": "滕王阁"
}
}
]
}
}
}
排序，范围,分页,filter是must的不计分版本
GET /building/_doc/_search
{
"from":0,
"size":1,
"query": {
"bool": {
"filter": [
{
"match": {
"name": "广东"
}
},
{
"range": {
"create_time": {
"from": "2006-01-01",
"to": "now"
}
}
}
]
}
}
}
必须注意
组合bool查询,bool中有must、must_not、should（or的意思），它们之间并不是&& 的关系，它们之是满足了评分会高，结果出来3个，有3个都满足的七星岩、鼎湖山、但白云山should的一个都不满足，它还是被搜出来，只是它评分最低，想要不搜出白云山，看下一例：
GET /building/_doc/_search
{
"query": {
"bool": {
"must": {"match": {"name": "广东"}},
"should":[
{"range":{"history_years":{"lt":17}}},
{"range":{"create_time":{"gte":"2007-06-01"}}}
]
}
}
}
should前面加个bool，该bool作为must的子节点，结果只出来2条
GET /building/_doc/_search
{
"query": {
"bool": {
"must": [
{"match": {"name": "广东"}},
{"bool":{
"should":[
{"range":{"history_years":{"lt":17}}},
{"range":{"create_time":{"gte":"2007-06-01"}}}
]
}
}
]
}
}
}
更啰嗦的写法，和上面一样，结果只出来2条
GET /building/_doc/_search
{
"query": {
"bool": {
"should":[
{
"bool":{
"must":[
{"match": {"name":"广东"}},
{"range": {"history_years":{"lt":"17"}}}
]
}
}
,
{
"bool":{
"must":[
{"match":{"name":"广东"}},
{"range":{"create_time":{"gte":"2007-06-01"}}}
]
}
}
]
}
}
}
组合bool查询,bool嵌套bool
GET /building/_doc/_search
{
"query": {
"bool": {
"should": [
{
"bool": {
"must": [
{"match": {"name": "广东"}},
{"range": {"history_years": {"gt": 4}}}
]
,
"must_not": {
"range": {"create_time": {"lte": "2007-01-01"}}
}
}
}
,
{
"term": {"shortname": "滕王阁"}
}
]
}
}
}
分词器将query分为2个单词，operator默认是or，如果用and，则没有结果出来
GET /building/_doc/_search
{
"query": {
"match": {
"name": {
"query": "uk italy",
"operator": "and"
}
}
}
}
2个should条件，最少满足2个，结果为空
GET /building/_doc/_search
{
"query": {
"bool": {
"should":[
{"match": {"name": "湖南"}},
{"match": {"name": "北京"}}
],
"minimum_should_match": 2
}
}
}
设置各字段的权重，总权重计算得分高
GET /building/_doc/_search
{
"query": {
"bool": {
"should":[
{
"match": {"name": {"query":"山","boost":10}}
},
{
"match": {"shortname": {"query":"滕王阁","boost":1}}
}
],
"minimum_should_match": 1
}
}
}
分离化最大得分dis_max
GET /building/_doc/_search
{
"query": {
"dis_max": {
"queries":[
{
"match": {"name":"山"}
},
{
"match": {"shortname": "滕王阁"}
}
]
}
}
}
内部对象模糊查询
GET /building/_doc/_search
{
"query":{
"match":{"famous_person.p_name":"山"}
}
}
多字段模糊查询
GET /building/_doc/_search
{
"query":{
"multi_match":{
"query": "山",
"fields":["famous_person.p_name","name"]
}
}
}
短语匹配
GET /building/_doc/_search
{
"query":{
"match_phrase":{
"famous_person.p_name":{
"query":"Hawking Dog",
"slop":3
}
}
}
}
前缀匹配。量大的时候谨慎使用，底层是遍历倒排索引
GET /building/_doc/_search
{
"query":{
"prefix":{
"name":"湖南"
}
}
}
通配前缀匹配，? 匹配任意字符， * 匹配 0 或多个字符。量大的时候谨慎使用，底层是遍历，避免使用左通配这样的模式匹配
GET /building/_doc/_search
{
"query":{
"wildcard":{
"name":"湖*"
}
}
}
短语前缀匹配
GET /building/_doc/_search
{
"query":{
"match_phrase_prefix":{
"famous_person.p_name":{
"query":"Hawking",
"slop":3
}
}
}
}
检查字段存在的记录=sql：is not null
GET /building/_doc/_search
{
"query":{
"exists":{"field":"name"}
}
}
对应存在，相反面的不存在，missing已经取消
GET /building/_doc/_search
{
"query":{
"bool":{
"must_not":{
"exists":{"field":"name"}
}
}
}
}
重构索引，导入旧到新的
PUT /building4new
{
"settings": {
"index": {
"analysis.analyzer.default.type": "ik_max_word"
},
"number_of_replicas": 2,
"number_of_shards": 1
},
"mappings": {
"dynamic": false,
"properties": {
"name": {
"type": "text"
},
"shortname": {
"type": "keyword"
},
"create_time": {
"type": "date",
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
},
"history_years": {
"type": "integer"
},
"famous_person": {
"dynamic": false,
"properties": {
"sex": {
"type": "keyword"
},
"age": {
"type": "integer"
},
"p_name": {
"type": "text"
}
}
}
}
}
}
POST _reindex
{
"source":{"index":"building"},
"dest":{"index":"building4new"}
}
GET /building4new/_doc/_search
#####重命名
PUT /building4new/_alias/haha
GET /haha/_doc/_search
高亮
GET /haha/_doc/_search
{
"query":{
"match":{
"shortname":"白云山"
}
},
"highlight":{
"fields":{
"shortname":{}
},
"pre_tags": ["<font color='red'>"],
"post_tags": ["</font>"]
}
}
查询时输入即搜索
PUT /my_suggester
{
"settings":{
"analysis":{
"analyzer":{
"default":{
"type":"ik_max_word"
}
}
}
},
"mappings":{
"dynamic_date_formats": [
"MM/dd/yyyy",
"yyyy/MM/dd HH:mm:ss",
"yyyy-MM-dd",
"yyyy-MM-dd HH:mm:ss"
],
"properties":{
"suggest":{
"type":"completion"
}
}
}
}
POST /my_suggester/_doc
{
"title":"天气",
"desc":"今天天气不错",
"suggest": {
"input": "天气"
}
}
POST /my_suggester/_doc
{
"title":"天空",
"desc":"蓝蓝的天空，白白的云",
"suggest": {
"input": "天空"
}
}
GET /my_suggester/_doc/_search
{
"suggest":{
"s-text":{
"prefix":"天",
"completion":{"field":"suggest"}
}
}
}
聚合
模拟数据
PUT /cars
{
"settings":{
"number_of_replicas":2,
"number_of_shards":1
},
"mappings":{
"properties":{
"price":{"type":"double"},
"color":{"type":"keyword"},
"make":{"type":"keyword"},
"sold":{"type":"date","format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"},
"remark":{"type":"text","analyzer":"ik_max_word"}
}
}
}
GET /cars/_mapping
POST /cars/_doc/_bulk
{ "index": {}}
{ "price" : 10000, "color" : "red", "make" : "honda", "sold" : "2014-10-28","remark":"honda red one" }
{ "index": {}}
{ "price" : 20000, "color" : "red", "make" : "honda", "sold" : "2014-11-05","remark":"honda red two" }
{ "index": {}}
{ "price" : 30000, "color" : "green", "make" : "ford", "sold" : "2014-05-18","remark":"ford green one" }
{ "index": {}}
{ "price" : 15000, "color" : "blue", "make" : "toyota", "sold" : "2014-07-02","remark":"toyota blue one" }
{ "index": {}}
{ "price" : 12000, "color" : "green", "make" : "toyota", "sold" : "2014-08-19","remark":"toyota green two" }
{ "index": {}}
{ "price" : 20000, "color" : "red", "make" : "honda", "sold" : "2014-11-05","remark":"honda red three" }
{ "index": {}}
{ "price" : 80000, "color" : "red", "make" : "bmw", "sold" : "2014-01-01","remark":"bmw red one" }
{ "index": {}}
{ "price" : 25000, "color" : "blue", "make" : "ford", "sold" : "2014-02-12","remark":"ford blue two" }
GET /cars/_doc/_search
make字段聚合分组数量
GET /cars/_doc/_search
{
"aggs": {
"my_gourp_make": {
"terms": {
"field": "make"
}
}
}
}
make字段聚合分组，不显示数据hits为空，只显示分组数量
GET /cars/_doc/_search
{
"size":0,
"aggs": {
"my_gourp_make": {
"terms": {
"field": "make"
}
}
}
}
以make分组统计其平均价格
GET /cars/_doc/_search
{
"aggs":{
"my_group_make":{
"terms":{"field":"make"},
"aggs":{
"my_avg_price":{
"avg":{"field":"price"}
}
}
}
}
}
以make分组统计其最大价格以及车的颜色分组数量,最贵和颜色是等级关系
GET /cars/_doc/_search
{
"size":0,
"aggs":{
"my_group_make":{
"terms":{"field":"make"},
"aggs":{
"my_max_price":{"max":{"field":"price"}},
"my_group_color":{"terms":{"field":"color"}}
}
}
}
}
复合版,先颜色分组，在求得各颜色的平均加钱，（平级关系）各个颜色的品牌的最大最小价格
GET /cars/_doc/_search
{
"size":0,
"aggs":{
"my_group_color":{
"terms":{
"field":"color"
},
"aggs":{
"my_avg_price":{
"avg":{
"field":"price"
}
},
"my_group_make":{
"terms":{
"field":"make"
},
"aggs":{
"max_price":{"max":{"field":"price"}},
"min_price":{"min":{"field":"price"}}
}
}
}
}
}
}
图形方式展示总价，每20000间隔一个
GET /cars/_doc/_search
{
"size" : 0,
"aggs":{
"price":{
"histogram":{
"field": "price",
"interval": 20000
},
"aggs":{
"revenue": {
"sum": {
"field" : "price"
}
}
}
}
}
}
价钱的各种统计维度
GET /cars/_doc/_search
{
"size": 0,
"aggs": {
"group_make": {
"terms": {
"field": "make",
"size": 5
},
"aggs": {
"my_stat": {
"extended_stats": {
"field": "price"
}
}
}
}
}
}
按月统计各品牌销售数量,min_doc_count,extended_bounds，没有销售数量的月份也返回0，空bucket
GET /cars/_doc/_search
{
"size": 0,
"aggs": {
"sales": {
"date_histogram": {
"field": "sold",
"interval": "month",
"format": "yyyy-MM-dd",
"min_doc_count" : 0,
"extended_bounds" : {
"min" : "2014-01-01",
"max" : "2014-12-31"
}
},
"aggs": {
"makes": {
"terms": {"field": "make"}
}
}
}
}
}
查询与聚合,honda品牌的车一共卖了多少钱
GET /cars/_doc/_search
{
"query":{
"term":{
"make":"honda"
}
},
"aggs":{
"haha":{
"sum":{
"field":"price"
}
}
}
}
局部与全局比较，用到全局桶 "global":{}
GET /cars/_doc/_search
{
"query":{
"term":{
"make":"honda"
}
},
"aggs":{
"single":{
"sum":{
"field":"price"
}
},
"all":{
"global":{},
"aggs":{
"all_sum":{
"sum":{"field":"price"}
}
}
}
}
}
过滤、搜索、聚合,先查出honda车，在选出2014-11-01后sold的车的总销售额
GET /cars/_doc/_search
{
"query": {
"term": {
"make": "honda"
}
},
"aggs": {
"filterAndAggs": {
"filter": {
"range": {
"sold": {
"from": "2014-11-01"
}
}
},
"aggs": {
"totalSum": {
"sum": {
"field": "price"
}
}
}
}
}
}
后过滤，选出ford车，再根据结果按颜色分组，最后在hits中筛选出绿色的车
GET /cars/_doc/_search
{
"query":{
"match":{
"make":"ford"
}
},
"aggs":{
"color_group":{
"terms":{
"field":"color"
}
}
},
"post_filter":{
"term":{"color":"green"}
}
}
内置排序,度量排序，反向order从下到上优先，先count后avg
GET /cars/_doc/_search
{
"size":0,
"aggs":{
"group make":{
"terms":{
"field":"make",
"order":{
"haha_avg":"desc",
"_count":"asc"
}
},
"aggs":{
"haha_avg":{
"avg":{
"field":"price"
}
}
}
}
}
}
distance的方式，不重复统计count
GET /cars/_doc/_search
{
"aggs":{
"distance":{
"cardinality":{
"field":"color"
}
}
}
}
sql转dsl
POST _sql?format=txt
{
"query":"select make,count(make),sum(price) as totalSum from cars group by make"
}
missing ，结果仅仅只有doc_count
GET /cars/_doc/_search
{
"size":0,
"aggs": {
"mizz": {
"missing": {
"field": "haha"
}
}
}
}
