sql和es之间的常用转化

1.match查询：会先对搜索词进行分词
2.match_phrase查询：match_phrase与slop一起用，能保证分词间的邻近关系，slop参数告诉match_phrase查询词条能够相隔多远时仍然将文档视为匹配，
默认是0。为0时 必须相邻才能被检索出来。
3.wildcard查询：是使用通配符进行查询，其中？代表任意一个字符*代表任意的一个或多个字符。

1.select * from user where name = "张三"
{
"query": {
"term": {
"clientname.keyword": {
"value": "中控国威f9",
"boost": 1
}
}
}
}
2.select * from user where name in ("张三","李四")
{
"query": {
"terms": {
"clientname.keyword": [
"中控国威f9",
"中控F7 PLUS"
],
"boost": 1
}
}
}
3.select * from user where name like "%liujjing%"
{
"size":100,
"query": {
"match": {
"clientname": {
"query": "中控"

}
}
}
}
4.去重方法一
{
"size":2000,

"query":{
"match_all":{

}
},
"collapse":{

"field":"clientname.keyword"
}
}
5.{
"query": {
"match_all": {}
},
"aggs": {
"uid_aggs": {
"terms": {
"field": "clientname",
"size": 1
},
"aggs": {
"uid_top": {
"top_hits": {
"sort": [
{
"clientname.keyword": {
"order": "desc"
}
}
],
"size": 1
}
}
}
}
},
"size": 0
}
6.去重方法二
{
"query":{
"match_all":{

}
},
"collapse":{
"field":"clientname.keyword"
}
}
7.根据条件删除
algae_mic_otu/_delete_by_query post
{
"query": {
"term": {
"applicationId": {
"value": "30048d83a97744d1a0f335c90970ef97",
"boost": 1
}
}
}
}

8.
{
"_source": ["zlsj_sqperson","type1"],
"query":
{
"wildcard": {
"type1.keyword":""

}
}
}
9.查询type1为空的（太阳能）
{
"_source": ["zlsj_sqperson","type1"],

"query": {
"bool": {
"must_not": {
"exists": {
"field": "type1"
}
}
}
}
}
10.查询type1不为空的
{
"query": {
"exists": {
"field": "type1"
}
}
}
11
GET energy_sci_2022/_search
{
"_source": ["title","type","address_info"],
"query":
{
"wildcard": {
"address_info.keyword":"*Chinese Acad Sci*"

}
}
}

12.解决text类型无法排序的问题
PUT energy_patent_incopat/energy_patent_incopat/_mapping
{
"properties": {
"zlsj_hxjzd": {
"type": "text",
"fielddata": true
}
}
}
13.select count(zlsj_ipc) as num ,zlsj_ipc from 3_cn_dynamic_20220427180756
where type1="煤炭高效燃烧" and type1 is not null group by zlsj_ipc order by num desc
{
"query": {
"bool": {
"must": [
{
"match_phrase": {
"type1.keyword": {
"query": "煤炭高效燃烧"
}
}
},
{
"bool": {
"must_not": [
{
"match_phrase": {
"type1": {
"query": ""
}
}
}
]
}
}
]
}
},
"from": 0,
"size": 0,
"aggregations": {
"zlsj_ipc": {
"aggregations": {
"COUNT(zlsj_ipc)": {
"value_count": {
"field": "zlsj_ipc.keyword"
}
}
},
"terms": {
"field": "zlsj_ipc.keyword",
"size": 200
}
}
}
}

14，update energy_patent set zlsj_sqperson='韩国LG电子' where 韩国LG电子='LG ELECTRONICS INC';
POST energy_patent/_update_by_query
{
"script": {
"inline": "ctx._source['zlsj_sqperson'] = '韩国LG电子'"
},
"query": {
"bool": {
"must": [
{
"term": {
"zlsj_sqperson.keyword": "LG ELENICS INC"
}
}
]
}
}
}

15.POST energy_patent/_update_by_query
{
"script": {
"source": "ctx._source.zlsj_sqperson='LG化学有限公司'"
},
"query": {
"bool": {
"must": [
{
"term": {
"zlsj_sqperson.keyword": "LG CHEM LTD"
}
}
]
}
}
}

16.delete from algae_mic_otu where dataName like '20220720_课题组二_微藻分子OUT%'
POST /algae_mic_otu/_delete_by_query
{
"query": {
"wildcard": {
"dataName.keyword":"202_微藻分子OUT*"

}

}
}