1. General
Reprint: https://elasticsearch.cn/article/629
When students with SQL background are learning Elasticsearch, facing a query demand, they can't help thinking about how to implement it with SQL first, and then how to implement the Query DSL of Elasticsearch. Then this article will tell you how to implement a common SQL statement with Elasticsearch query language.
1. SQL statement
Suppose we have a data set of cars. Each car has fields such as model and color. I want to get the first two models with more than one color category. It is assumed that the data model of the vehicle is as follows:
{
"model":"modelA",
"color":"red"
}
Suppose we have a cars table and create test data through the following statement.
INSERT INTO cars (model,color) VALUES ('A','red');
INSERT INTO cars (model,color) VALUES ('A','white');
INSERT INTO cars (model,color) VALUES ('A','black');
INSERT INTO cars (model,color) VALUES ('A','yellow');
INSERT INTO cars (model,color) VALUES ('B','red');
INSERT INTO cars (model,color) VALUES ('B','white');
INSERT INTO cars (model,color) VALUES ('C','black');
INSERT INTO cars (model,color) VALUES ('C','red');
INSERT INTO cars (model,color) VALUES ('C','white');
INSERT INTO cars (model,color) VALUES ('C','yellow');
INSERT INTO cars (model,color) VALUES ('C','blue');
INSERT INTO cars (model,color) VALUES ('D','red');
INSERT INTO cars (model,color) VALUES ('A','red');
The SQL statement required by us is also relatively simple. The implementation is as follows:
SELECT model,COUNT(DISTINCT color) color_count FROM cars GROUP BY model HAVING color_count > 1 ORDER BY color_count desc LIMIT 2;
In this query statement, Group By is grouped by model, having color_ Count > 1 defines that the type of vehicle color is greater than 1, ORDER BY color_count desc restricts the results to be arranged in reverse order according to the color type, while LIMIT 2 restricts only the first three pieces of data to be returned.
So how to implement this requirement in Elasticsearch?
2. Simulate test data in Elasticsearch
First, we need to insert the test data into elasticsearch. Here we use the bulk interface, as shown below:
POST _bulk
{"index":{"_index":"cars","_type":"doc","_id":"1"}}
{"model":"A","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"2"}}
{"model":"A","color":"white"}
{"index":{"_index":"cars","_type":"doc","_id":"3"}}
{"model":"A","color":"black"}
{"index":{"_index":"cars","_type":"doc","_id":"4"}}
{"model":"A","color":"yellow"}
{"index":{"_index":"cars","_type":"doc","_id":"5"}}
{"model":"B","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"6"}}
{"model":"B","color":"white"}
{"index":{"_index":"cars","_type":"doc","_id":"7"}}
{"model":"C","color":"black"}
{"index":{"_index":"cars","_type":"doc","_id":"8"}}
{"model":"C","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"9"}}
{"model":"C","color":"white"}
{"index":{"_index":"cars","_type":"doc","_id":"10"}}
{"model":"C","color":"yellow"}
{"index":{"_index":"cars","_type":"doc","_id":"11"}}
{"model":"C","color":"blue"}
{"index":{"_index":"cars","_type":"doc","_id":"12"}}
{"model":"D","color":"red"}
{"index":{"_index":"cars","_type":"doc","_id":"13"}}
{"model":"A","color":"red"}
Where index is cars and type is doc, and all data is consistent with mysql data. You can execute the above command in Kibana Dev Tools, and then execute the following query statement to verify whether the data has been successfully stored.
GET cars/_search
3. Group By VS Terms/Metric Aggregation
The Group By statement in SQL corresponds to Terms Aggregation in Elasticsearch, that is, bucket aggregation. The statement corresponding to Group By color is as follows:
GET cars/_search
{
"size":0,
"aggs":{
"models":{
"terms":{
"field":"model.keyword"
}
}
}
}
The results are as follows:
{
"took": 161,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 13,
"max_score": 0,
"hits": []
},
"aggregations": {
"models": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "A",
"doc_count": 5
},
{
"key": "C",
"doc_count": 5
},
{
"key": "B",
"doc_count": 2
},
{
"key": "D",
"doc_count": 1
}
]
}
}
}
Let's look at the key of aggregations. The following is the returned result.
Another item in the SQL statement is COUNT(DISTINCT color) color_count is used to calculate the number of colors of each model. In Elasticsearch, we need to use an indicator class to aggregate Cardinality and count different values. The statement is as follows:
GET cars/_search
{
"size": 0,
"aggs": {
"models": {
"terms": {
"field": "model.keyword"
},
"aggs": {
"color_count": {
"cardinality": {
"field": "color.keyword"
}
}
}
}
}
}
The returned results are as follows:
{
"took": 74,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 13,
"max_score": 0,
"hits": []
},
"aggregations": {
"models": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "A",
"doc_count": 5,
"color_count": {
"value": 4
}
},
{
"key": "C",
"doc_count": 5,
"color_count": {
"value": 5
}
},
{
"key": "B",
"doc_count": 2,
"color_count": {
"value": 2
}
},
{
"key": "D",
"doc_count": 1,
"color_count": {
"value": 1
}
}
]
}
}
}
Color in results_ Count is the number of colors of each model, but all models here have returned. We only want models with color number greater than 1, so we need to add a filter condition here.
4. Having Condition VS Bucket Filter Aggregation
Having color_ Count > 1 corresponds to Bucket Filter aggregation in Elasticsearch. The statement is as follows:
GET cars/_search
{
"size": 0,
"aggs": {
"models": {
"terms": {
"field": "model.keyword"
},
"aggs": {
"color_count": {
"cardinality": {
"field": "color.keyword"
}
},
"color_count_filter": {
"bucket_selector": {
"buckets_path": {
"colorCount": "color_count"
},
"script": "params.colorCount>1"
}
}
}
}
}
}
The returned results are as follows:
{
"took": 39,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 13,
"max_score": 0,
"hits": []
},
"aggregations": {
"models": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "A",
"doc_count": 5,
"color_count": {
"value": 4
}
},
{
"key": "C",
"doc_count": 5,
"color_count": {
"value": 5
}
},
{
"key": "B",
"doc_count": 2,
"color_count": {
"value": 2
}
}
]
}
}
}
At this time, the returned result only contains models with more than 1 colors, but you will find that C with more colors is not in the first position, and we still need to sort.
5. Order By Limit VS Bucket Sort Aggregation
ORDER BY color_count desc LIMIT 3 can be implemented by Bucket Sort aggregation in Elasticsearch. The statement is as follows:
GET cars/_search
{
"size": 0,
"aggs": {
"models": {
"terms": {
"field": "model.keyword"
},
"aggs": {
"color_count": {
"cardinality": {
"field": "color.keyword"
}
},
"color_count_filter": {
"bucket_selector": {
"buckets_path": {
"colorCount": "color_count"
},
"script": "params.colorCount>1"
}
},
"color_count_sort": {
"bucket_sort": {
"sort": {
"color_count": "desc"
},
"size": 2
}
}
}
}
}
}
The returned results are as follows:
{
"took": 32,
"timed_out": false,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 13,
"max_score": 0,
"hits": []
},
"aggregations": {
"models": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "C",
"doc_count": 5,
"color_count": {
"value": 5
}
},
{
"key": "A",
"doc_count": 5,
"color_count": {
"value": 4
}
}
]
}
}
}
So far, we have realized the function of SQL statement with Elasticsearch query statement. Comparing the SQL statement with the query statement of Elasticsearch, you will find that the latter is much more complex, but not without rules to follow. As you become more and more familiar with common grammar, I believe it will be more and more handy!