ES 语句
>d当前语句均为在Kibana中使用的语句
>iDate：06-04-2021
所有演示语句，均以'i_iot_run'索引(表)为例
>d**除查询外，索引操作若要执行，请自建测试索引，测试完后，请删除所建索引。**
>iES查询用到的包，当前用到的，并不是所有。
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.MatchQueryBuilder;
import org.elasticsearch.index.query.RangeQueryBuilder;
import org.elasticsearch.index.query.TermsQueryBuilder;
import org.elasticsearch.search.aggregations.Aggregation;
import org.elasticsearch.search.aggregations.AggregationBuilders;
import org.elasticsearch.search.aggregations.Aggregations;
import org.elasticsearch.search.aggregations.PipelineAggregatorBuilders;
import org.elasticsearch.search.aggregations.bucket.terms.ParsedStringTerms;
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
import org.elasticsearch.search.aggregations.bucket.terms.TermsAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.AvgAggregationBuilder;
import org.elasticsearch.search.aggregations.metrics.SumAggregationBuilder;
import org.elasticsearch.search.aggregations.pipeline.BucketSortPipelineAggregationBuilder;
import org.elasticsearch.search.aggregations.pipeline.ParsedSimpleValue;
import org.elasticsearch.search.sort.FieldSortBuilder;
import org.elasticsearch.search.sort.SortOrder;
import org.springframework.data.elasticsearch.core.ElasticsearchRestTemplate;
import org.springframework.data.elasticsearch.core.SearchHits;
import org.springframework.data.elasticsearch.core.query.NativeSearchQueryBuilder;
### 添加索引
添加数据前，不提前创建索引，数据也可以插入，但是格式不受控制。
```
只添加索引
PUT i_iot_run
添加索引，并设置shard和mapping
PUT i_iot_run
{
"setting":{
"number_of_replicas": 0
},
"mapping":{
"properties": {
"endTime": {
"type": "date",
"format": "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
},
"id": {
"type":"long"
},
"imei": {
"type":"keyword"
},
"intervals": {
"type":"long"
},
"startTime": {
"type":"date",
"format":"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
},
"memberId": {
"type":"long"
},
"projectId": {
"type":"long"
},
"partnerId": {
"type":"long"
},
"memberTypeName": {
"type":"keyword"
},
"dataAuthority": {
"role":{
"type":"text",
"keyword":{
"type": "keyword",
"ignore_above": 256
}
},
"depart":{
"type": "long"
},
},
"regionId": {
"type":"long"
},
"workUnit": {
"type":"keyword"
},
"workAmount": {
"type":"float"
}
}
}
}
```
---
### 修改索引所在shard
```
POST _reindex
{
"source": {
"index": "i_iot_run"
},
"dest": {
"index": "i_iot_run_0"
}
}
```
---
### 查看索引mapping
```
GET i_iot_run/_mapping
```
---
### 查询有多少索引
```
GET _cat/indices?v
```
---
### 查询索引中所有数据
```
GET i_iot_run/_search
OR
GET i_iot_run/_search{
"query": {
"match_all": {}
}
}
#默认返回10条记录。
```
---
### in & is
```
GET i_iot_run/_search
{
"query": {
"terms": {
"memberId": [
333,
933
]
}
}
}
{
"query": {
"term": {
"memberId": 333
}
}
}
terms : 匹配一个字段的多个值。 可理解为 => in
term:匹配一个字典的一个值。 可理解为 => is
```
---
### in & is 对应的JAVA代码
```
in
TermsQueryBuilder matchIotCodes = new TermsQueryBuilder("dataAuthority.depart", String.valueOf(memberId));
is
MatchQueryBuilder matchMemberId = new MatchQueryBuilder("memberId", memberId);
TermQueryBuilder matchIotCodes = new TermQueryBuilder("dataAuthority.depart", String.valueOf(memberId));
完整代码请查看相关项目。
```
Match：会对查询的关键词进行分词，然后根据分词后的结果，进行匹配。
Term：直接使用关键词查询，不会分词。
---
### or查询
```
GET p_electron_card/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"dataAuthority.depart": [
585
]
}
},
{
"bool": {
"should": [
{
"match": {
"nickName": "测试"
}
},
{
"match": {
"imei": "测试"
}
},
{
"match": {
"personalPhone": "测试"
}
}
]
}
}
],
"must_not": [
{
"term": {
"validState": 0
}
}
]
}
}
}
@modify 03/30/2022
```
以上代码片段中，重要的一点是，第二层'bool'块。第二层'bool'中的'should'中的must，是or查询的子项。如果直接使用第二层'bool'块，则无法达到or查询的效果。
---
### Or查询对应Java代码
```
// 构建or查询容器
BoolQueryBuilder orBool = new BoolQueryBuilder();
// 构建or查询子项
MatchQueryBuilder matchMemberId = new MatchQueryBuilder("memberId", memberId); // 匹配顶级机构ID
MatchQueryBuilder matchRegionId = new MatchQueryBuilder("regionId", memberId); // 匹配大区ID
MatchQueryBuilder matchProjectId = new MatchQueryBuilder("projectId", memberId); // 匹配项目ID
orBool.should(matchMemberId);
orBool.should(matchRegionId);
orBool.should(matchProjectId);
or查询，需要放入到bool.must中。
完整代码，请查看相关项目
```
---
### 范围查询
```
GET i_iot_run/_search
{
"query": {
"range": {
"startTime": {
"gte":"2021-06-01T00:00:00.000Z",
"lte":"2021-06-01T23:59:59.999Z"
}
}
}
}
```
### 范围查询对应Java代码
```
// 时间范围查询
RangeQueryBuilder datetimeRange = new RangeQueryBuilder("startTime") // 查询条件，范围
.gte(begin)
.lte(end)
.format(Constant.UTC_DATE_FORMAT);
// 将范围查询，放入外层bool.filter中。
boolQueryBuilder.filter(datetimeRange);
完整上下文代码，请查看相关项目。
```
---
### 多条件查询
```
#查询时间在6.1号，并且物联网编码为"868120237173650","868120237174138","868120246402850","868120246397340"的设备信息
GET i_iot_run/_search
{
"query":{
"bool":{
"must":[
{
"terms":{
"imei":[
"868120237173650",
"868120237174138",
"868120246402850",
"868120246397340"
]
}
}
],
"filter":[
{
"range": {
"startTime": {
"gte":"2021-06-01T00:00:00.000Z",
"lte":"2021-06-01T23:59:59.999Z"
}
}
}
]
}
}
"size": 200
}
#size: 返回数据集合大小。默认10条。
```
---
### 索引中添加字段
```
POST i_iot_run/_mapping
{
"properties": {
"workUnit2": {
"type": "keyword"
}
}
}
```
---
### 批量插入数据
```
POST i_iot_run/_bulk
{"index":{"_id":1}}
{"data":{"imei":"....","memberId":1,"projectId":1.....}}
{"index":{"_id":2}}
{"data":{"imei":"....","memberId":2,"projectId":2.....}}
{"index":{"_id":3}}
{"data":{"imei":"....","memberId":3,"projectId":3.....}}
_bulk:_bulk是批量操作API，不仅可以执行批量插入。
```
>d 尝试批量操作，请自建测试索引。
---
### 模糊查询
```
GET i_iot_run
{
"query": {
"wildcard": {
"memberTypeName": "J*"
}
}
}
```
>i1：J* => 以'J'开头
2：*J:=> 以'J'结尾
3：\*J\* => 包含'J'
>d**模糊查询数据量越大效率越低，当查询内容较多，数据量较大时建议将该字段设置成text进行分词，然后通过match进行匹配。**
---
### 模糊查询对应Java代码
```
**.must(QueryBuilders.termsQuery(fieldName, value));
```
>i当前项目中并没有使用到模糊查询，若用到模糊查询，请自行在项目中尝试。
---
### 聚合查询-分组
```
GET i_iot_run/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"imei": ["...","..."]
}
}
],
"filter": {
"range": {
"startTime": {
"gte":"..",
"lte":".."
}
}
}
}
},
"size": 0,
"aggs": {
"group_by_***": {
"terms": {
"field": "workUnit"
},
"aggs": {
"group_by_***": {
"terms": {
"field": "projectId"
}
}
}
}
}
}
```
>d**由于不能出现重复的标签。(一个语句块中，不能出现两个'aggs')，所以分组都是包含的关系。若有多个，继续往下嵌套追加即可。但一定要想清楚其中的关系。**
在Java中，要使用嵌套循环，来获取分组后的数据。这里嵌套了几层'aggs'，Java中就嵌套循环几层。
---
### 聚合查询-求和
```
GET i_iot_run/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"imei": ["...","..."]
}
}
],
"filter": {
"range": {
"startTime": {
"gte":"..",
"lte":".."
}
}
}
}
},
"size": 0,
"aggs": {
"group_by_***": {
"terms": {
"field": "workUnit"
},
"aggs": {
"group_by_***": {
"terms": {
"field": "projectId"
}
}，
"aggs": {
"runTime": {
"sum": "intervals"
}
}
}
}
}
}
# 最后一个'aggs'中，runTime为别名，同SQL中的as('runTime'),'sum'是使用'sum'API进行计算。'intervals'是要进行计算的字段。
```
---
### 聚合查询-计算
```
GET i_iot_run/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"imei": ["...","..."]
}
}
],
"filter": {
"range": {
"startTime": {
"gte":"..",
"lte":".."
}
}
}
}
},
"size": 0,
"aggs": {
"group_by_***": {
"terms": {
"field": "workUnit"
},
"aggs": {
"group_by_***": {
"terms": {
"field": "projectId"
}
}，
"aggs": {
"runTime": {
"sum": "intervals"
},
"hours": {
"bucket_script": {
"bucket_path": {
"_runTime": "runTime"
},
"script": "(float) (params._runTime / 3600000)"
}
}
}
}
}
}
}
```
>i最后一个'aggs'中，hours为别名，同SQL中的as('hours'),'bucket_script'是使用'script'脚本进行计算，'bucket_path' 是使用字段的路径，_runTime是别名，runTime是上面聚合的别名，_runTime 指向 runTime。
script 中的语句，使用路径中的变量时，切记一定要加前缀'params.',否则找不到。
---
### 执行查询
```
SearchHits<IotRunData> iotRunDataSearchHits = elasticsearchRestTemplate.search(queryBuilder.build(), IotRunData.class);
```
---
### 修改
```
POST i_iot_run/_update_by_query
{
"query": {
"bool": {
"must": [
{
"terms": {
"imei": [
"868120272193621"
]
}
},
{
"terms": {
"regionName": [
"融创南京大区"
]
}
}
]
}
},
"script": {
"source": "ctx._source.regionName = params['regionName']",
"params": {
"regionName":"融创华北大区"
},
"lang": "painless"
}
}
```
根据查询条件修改。
上面代码片段中，是根据物联网编码和大区查找数据，然后修改大区名称。
'ctx._source' 是固定格式
'.regionName' 是字段名
'params' 对应下面的参数列表
---
#### 数组中追加元素
```
POST test_cross/_update_by_query
{
"query": {
"bool": {
"must": [
{
"terms": {
"imei": [
"345345345"
]
}
}
]
}
},
"script": {
"source": "ctx._source.dataAuthority.depart.add(params.depart)",
"params": {
"depart":3
},
"lang": "painless"
}
}
```
追加元素时，使用add()添加。
---
#### 数组中删除某个值
```
POST test_cross/_update_by_query
{
"query": {
"bool": {
"must": [
{
"terms": {
"imei": [
"345345345"
]
}
}
]
}
},
"script": {
"source": "ctx._source.dataAuthority.depart.remove(ctx._source.dataAuthority.depart.indexOf(params.depart))",
"params": {
"depart":3
},
"lang": "painless"
}
}
```
删除时需要用indexOf找到元素，然后使用remove移除，直接remove会报错。
```
{
"script": {
"source": "ctx._source.comments.add(params.new_comment)",
"params": {
"new_comment": {
"name": "xiang",
"age": 25,
"rating": 18,
"comment": "very very good article...",
"commented_on": "3 Nov 2018"
}
}
}
}
```
添加对象
```
{
"script": {
"lang": "painless",
"source": "ctx._source.comments.removeIf(it -> it.name == 'John');"
}
}
```
移除数组中的一个对象
---
### 夸字段搜索
```
GET b_equipment_entity/_search
{
"query": {
"bool": {
"must": [
{
"terms": {
"dataAuthority": [
989
]
}
},
{
"multi_match": {
"query": "战神530 53000285 401530",
"fields": ["memberTypeName","equipmentNo","imei"]
}
}
]
}
}
}
```
### 根据聚合后字段进行排序
应该可以多字段排序，没有进行测试，有兴趣的可以测试一下。猜测排序的顺序应该跟添加到list中的顺序有一定关系。
```
...
/* 聚合内排序 step:1 */
List<FieldSortBuilder> sorts = new ArrayList<>();
/* 聚合内排序 step:2 */
sorts.add(SortBuilders.fieldSort("hours").order(SortOrder.DESC));
sourceBuilder.aggregation(
group_by_iotCode
.subAggregation(runTime)
.subAggregation(PipelineAggregatorBuilders.bucketScript("hours", hoursMap, hoursScript))
.subAggregation(project)
/* 聚合内排序 step:3 */
.subAggregation(PipelineAggregatorBuilders.bucketSort("hourSort",sorts))
);
...
```
### 删除索引
```
DELETE i_iot_run
```
---