ES查询语句
select * from tablename
GET tablename/_search
{"query": {
"match_all": {}
}}
select * from tablename where e_id = "XXX"
GET tablename/_search
{"query": {"term": {
"eId": {
"value": "XXX"
}
}}}
'#逻辑运算符,布尔运算符[NOT,AND,OR]
'#bool包裹,相当于括号
'#NOT
select * from tablename where e_id != "XXX"
GET tablename/_search
{
"query": {"bool": {"must_not": [
{"term": {
"eId": {
"value": "XXX"
}
}}
]}}
}
select * from tablename where e_id in ("XXX", "YYY") and state = 2
GET tablename/_search
{
"query": {
"bool": {"must": [
{"terms": {
"eId": [
"XXX",
""
]
}},{"term": {
"state": {
"value": "2"
}
}}
]}
}
}
ORDER BY
select * from tablename order by create_time desc
GET tablename/_search
{
"sort": [
{
"createTime": {
"order": "desc"
}
}
]
}
'# 查询指定字段
select comapnyName, source from tablename
GET tablename/_search
{
"_source": {"includes": ["companyName", "source"]}
}
'# LIMIT
select * from tablename limit 100
GET tablename/_search
{
"size": 100
, "from": 1
}
'# OR
select * from tablename where e_id in ("A","B") or (e_id = C and y_id = c)
GET tablename/_search
{"query": {
"bool": {"should": [
{"terms": {
"eId": [
"A",
"B"
]
}},{"bool": {"must": [
{"term": {
"eId": {
"value": "C"
}
}},{"term": {
"yList.yId.keyword": {
"value": "c"
}
}}
]}}
]}
}}
'# SUM
select SUM(e_amount) from tablename
GET tablename/_search
{
"size": 0,"aggs": {
"win": {
"sum": {
"field": "eAmount"
}
}
}
}
'# NULL
select * from tablename where f_time is not Null
GET tablename/_search
{"query": {"exists":{"field": "fTime"}}}
'# 查看所有索引：
show databases
GET _cat/indices
'# GROUP BY
select SUM(e_amount) from tablename group by d_name
GET tablename/_search
{
"size": 0, "query": {"range": {
"eTime": {
"gte": "2021-07-01 00:00",
"lte": "2021-07-31 23:59"
}
}},
"aggs": {
"dName": {
"terms": {
"field": "dName",
"size": 10
},
"aggs": {
"d_win": {
"sum": {
"field": "eAmount"
}
}
}
}
}
}
SELECT * FROM tablename WHERE id like '12315341%';
{ "query": { "prefix": { "id": "12315341" } } }
五.更新数据
'# 更新指定字段
UPDATE tablenameSET uId = null WHERE id in ("A","B")
POST tablename/_update_by_query
{
"query": {
"terms": {
"_id": [
"A",
"B" ]
}
},
"script": {
"inline": "ctx._source.userId=null"
}
}
SQL转ES查询DSL语句：http://www.ischoolbar.com/EsParser/
六.工具
（1）Kibana：默认端口为5601
（2）接口请求工具postman：
URL(表路径):http://192.168.x.x:9200/_index/_type
Content-Type:application/json
两种方式:指定行(id),使用query
新增
PUT+URL
POST+URL/_id(id存在即编辑)
删除
POST+URL/_delete_by_query
DELETE+URL/_id
修改
POST+URL/_update_by_query
POST+URL/_update
POST+URL/_id(存在的id)
查询
GET+URL/_search
GET+URL/_id