1、两个条件and 问题:
SELECT * FROM t_test_info t WHERE t.kv.p.keyword = '123' AND t.kv.b.keyword = 'p'
如果想看详情数据设置size
GET /t_test_info/_search
{
"size": 0,
"from": 0,
"query": {
"bool": {
"must": [
{
"term": {
"kv.p.keyword": "123"
}
},
{
"term": {
"kv.b.keyword": "p"
}
}
],
"must_not": [],
"should": []
}
},
"sort": [],
"aggs": {}
}
结果:
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 4,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
}
}
2、同一字段 in问题:
SELECT * FROM t_test_info t WHERE t.client_ip in ('123' , '1234')
GET /t_test_info/_search
{
"size": 0,
"from": 0,
"query": {
"terms": {
"client_ip": [
"123","1234"
]
}
},
"sort": [],
"aggs": {}
}
结果:
{
"took" : 1,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 9,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
}
}
3、两字段 or 问题:
SELECT * FROM t_test_info t WHERE t.client_ip = '123' or t.kv.b = 'l'
GET /t_test_info/_search
{
"query": {
"bool": {
"should": [
{
"term": {
"client_ip": "123"
}
},
{
"term": {
"kv.b": "l"
}
}
],
"must_not": [],
"must": []
}
},
"from": 0,
"size": 0,
"sort": [],
"aggs": {}
}
结果:
{
"took" : 699,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 24,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
}
}
4、一个字段 not in 问题:
SELECT * FROM t_test_info t WHERE t.client_ip not in ('123', '1234')
GET /t_test_info/_search
{
"query": {
"bool": {
"must_not": [
{
"term": {
"client_ip": "123"
}
}
],
"must": [],
"should": []
}
},
"from": 0,
"size": 0,
"sort": [],
"aggs": {}
}
5、两个字段 not in 问题:
SELECT * FROM t_test_info t WHERE t.client_ip not in ('123') and t.@timestamp not in ("2020-05-15T15:13:47.000Z");
GET /t_test_info/_search
{
"query": {
"bool": {
"must_not": [
{
"term": {
"client_ip": "123"
}
},
{
"term": {
"@timestamp": "2020-05-15T15:13:47.000Z"
}
}
],
"should": []
}
},
"from": 0,
"size": 0,
"sort": [],
"aggs": {}
}
结果:
{
"took" : 7,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 343,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
}
}
6、复杂查询,多条件后group by问题:
SELECT count(kv.g) FROM t_test_info t WHERE t.kv.b = 'h' and t.@timestamp >= ("2020-01-01") and t.@timestamp =< ("2020-07-15") and kv.g is not null group by t.kv.g ;
GET t_test_info/_search
{
"size":0,
"query":{
"bool":{
"must":[{
"exists":{
"field":"kv.g"
}
},{
"term": {
"kv.b": {
"value": "h"
}
}
},{
"range": {
"@timestamp": {
"gte": "2020-01-01",
"lte": "2020-07-15"
}
}
}]
}
},
"aggs": {
"kv": {
"terms": {
"field": "kv.g.keyword"
}
}
}
}
结果默认输出10个结果:
{
"took" : 3,
"timed_out" : false,
"_shards" : {
"total" : 3,
"successful" : 3,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 402,
"relation" : "eq"
},
"max_score" : null,
"hits" : [ ]
},
"aggregations" : {
"kv" : {
"doc_count_error_upper_bound" : 0,
"sum_other_doc_count" : 10,
"buckets" : [
{
"key" : "a",
"doc_count" : 132
},
{
"key" : "b",
"doc_count" : 117
},
{
"key" : "c",
"doc_count" : 58
},
{
"key" : "d",
"doc_count" : 52
},
{
"key" : "e",
"doc_count" : 12
},
{
"key" : "f",
"doc_count" : 6
},
{
"key" : "g",
"doc_count" : 5
},
{
"key" : "h",
"doc_count" : 4
},
{
"key" : "i",
"doc_count" : 3
},
{
"key" : "j",
"doc_count" : 3
}
]
}
}
}
更新:0715 后续持续更新。
ElasticSearch的简单API操作可以查看我另外的一篇文档: