Solve Elasticsearch complex sort using script
Tags: elasticsearch, kibana, sql
Published: 2020-04-11 11:54:54

# Overview
One day, I had to write program of complex sort in elasticsearch. 
At first, I tries to operate score field but I noticed that it didn't work and it cannot explain what I want to do.
Therefore, I used script to solve this question. This article is a report of this solution.


# Environment
※use docker container
- Elasticsearch version 7.1.0
- Kibana version 7.1.0


# Background
I thought simple example for you.
What do you choose cloud service? AWS? Azure?


To take an example, 4 companies using cloud service.
- A company
 - main cloud servie : AWS
 - sub cloud service: GCP, Azure

- B company
 - main : GCP
 - sub : AWS

- C company
 - main : GCP
 - sub : AWS, Azure

- D company
 - main : Azure
 - sub : GCP, AWS

| company name | main    | sub         |
|:------------:|:------: |:-----------:|
|       A      |  AWS    | GCP, Azure  |
|       B      |  GCP    | AWS         |
|       C      |  GCP    | AWS, Azure  |
|       D      |  Azure  | GCP, AWS    | 


If I input "Azure", I expect hitting company names using Azure． To sum up, A, C and D. 
Additionally, I want to add 1 search option of sort. If input value is equal to main cloud service, it's on top in search result. On the contrary, if input value is equal to sub cloud service,  it's on under.
Finally, search result I expect is D, A and C.

| input value | expect result |
|:-----:|:------------: |
| Azure | D, A, C       |
| AWS   |  A,B,C,D      |
| GCP   |  B,C,A,D      |

※If hitting column is same, random order.
- Azure : (sub)A and C
- AWS : (sub)B, C and D
- GCP : (main)B and C. (sub)A and D

# In the case of database(MySQL)
It was difficult for me to think elasitcsearch code, so that I decided to think about using database.

※use docker container
- mysql version 5.7


### Preparation


①Create table.

```sql
CREATE TABLE cloud_service (
  id INT PRIMARY KEY,
  company_name VARCHAR(50),
  main_name VARCHAR(30)
);

CREATE TABLE cloud_service_sub (
  id INT PRIMARY KEY,
  cloud_service_main_id INT,
  sub_name VARCHAR(30)
);
```

②Insert table data.

```sql
INSERT INTO cloud_service (id, company_name, main_name) VALUES
  (1, 'A', 'AWS'),
  (2, 'B', 'GCP'),
  (3, 'C', 'GCP'),
  (4, 'D', 'Azure');

INSERT INTO cloud_service_sub VALUES
  (1, 1, 'GCP'）,
  (2, 1, 'Azure'）,
  (3, 2, 'AWS'）,
  (4, 3, 'AWS'）,
  (5, 3, 'Azure'）,
  (6, 4, 'GCP'）,
  (7, 4, 'AWS');
```

③Check table data.

```sql
SELECT * FROM cloud_service;

+----+--------------+-----------+
| id | company_name | main_name |
+----+--------------+-----------+
|  1 | A            | AWS       |
|  2 | B            | GCP       |
|  3 | C            | GCP       |
|  4 | D            | Azure     |
+----+--------------+-----------+
4 rows in set (0.00 sec)

SELECT * FROM cloud_service_sub;

+----+-----------------------+----------+
| id | cloud_service_main_id | sub_name |
+----+-----------------------+----------+
|  1 |                     1 | GCP      |
|  2 |                     1 | Azure    |
|  3 |                     2 | AWS      |
|  4 |                     3 | AWS      |
|  5 |                     3 | Azure    |
|  6 |                     4 | GCP      |
|  7 |                     4 | AWS      |
+----+-----------------------+----------+
7 rows in set (0.00 sec)
```

### sort SQL sentence
When I ran the following SQL, I got expected result.
"ORDER BY CASE ~" sentence is an extremely important point.

When input value is equal to main.main_name, this record sort no is 0. When input value isn't equal to main.main_name, this record sort no is 1.
Then, By using "ASC", main → sub.

```sql
SELECT DISTINCT company_name, main.main_name
FROM cloud_service main 
LEFT JOIN cloud_service_sub sub ON main.id = sub.cloud_service_main_id
WHERE main.main_name = "Azure" OR sub.sub_name = "Azure"
ORDER BY 
CASE main.main_name 
WHEN "Azure" THEN 0 
ELSE 1 END 
ASC;

+--------------+-----------+
| company_name | main_name |
+--------------+-----------+
| D            | Azure     |
| A            | AWS       |
| C            | GCP       |
+--------------+-----------+
3 rows in set (0.00 sec)
```

```sql
SELECT DISTINCT company_name, main.main_name
FROM cloud_service main 
LEFT JOIN cloud_service_sub sub ON main.id = sub.cloud_service_main_id
WHERE main.main_name = "AWS" OR sub.sub_name = "AWS"
ORDER BY 
CASE main.main_name 
WHEN "AWS" THEN 0 
ELSE 1 END 
ASC;

+--------------+-----------+
| company_name | main_name |
+--------------+-----------+
| A            | AWS       |
| C            | GCP       |
| D            | Azure     |
| B            | GCP       |
+--------------+-----------+
4 rows in set (0.00 sec)
```

```sql
SELECT DISTINCT company_name, main.main_name
FROM cloud_service main 
LEFT JOIN cloud_service_sub sub ON main.id = sub.cloud_service_main_id
WHERE main.main_name = "GCP" OR sub.sub_name = "GCP"
ORDER BY CASE main.main_name 
WHEN "GCP" THEN 0 
ELSE 1 END 
ASC;

+--------------+-----------+
| company_name | main_name |
+--------------+-----------+
| C            | GCP       |
| B            | GCP       |
| A            | AWS       |
| D            | Azure     |
+--------------+-----------+
4 rows in set (0.00 sec)
```

# In the case of Elasticsearch(operating kibana console)

### Preparation

①create template
```json
PUT /_template/my_template?pretty
{
  "index_patterns": "mytest",
  "settings": {
    "number_of_shards": 1
  },
  "mappings": {
    "properties": {
      "company_name": {
        "type": "keyword"
      },
      "main_cloud_name": {
        "type": "keyword"
      },
      "sub_cloud_name": {
        "type": "keyword"
      }
    }
  }
}
```

②post data
```json
POST /mytest/_doc
{
  "company_name" : "A",
  "main_cloud_name": "AWS",
  "sub_cloud_name": ["GCP","Azure"]
}

POST /mytest/_doc
{
  "company_name" : "B",
  "main_cloud_name": "GCP",
  "sub_cloud_name": ["AWS"]
}

POST /mytest/_doc
{
  "company_name" : "C",
  "main_cloud_name": "GCP",
  "sub_cloud_name": ["AWS", "Azure"]
}

POST /mytest/_doc
{
  "company_name" : "D",
  "main_cloud_name": "Azure",
  "sub_cloud_name": ["GCP", "AWS"]
}
```

③check document
```json
GET /mytest/_search?pretty
```

### sort Elasticsearch search API

When I ran the following search API, I got expected result.
`"_script"` sentence is an extremely important point.

`"type"` : This is the type using sort. I can choose `"string"` or `"number"`.

`"lang"` : `"painless"` is original langrage of Elasticsearch and can use part of java API.(Sorry, I don't know this in detail. Maybe, `"painless"` is java base.)

`"source"` : I can write the script of `"lang"`. The mechanism of script contents is the same as `sort SQL sentence`

```json
GET /mytest/_search?pretty
{
  "query": { 
    "bool" : {
      "should" : [
        {
          "term" : {
            "main_cloud_name" : "Azure"
          }
        },
        {
          "term" : {
            "sub_cloud_name" : "Azure"
          }
        }
      ]
    }
  },
  "sort" : {
    "_script" : {
      "type" : "number",
      "script" : {
        "lang": "painless",
        "source": """
            if (params.main == (doc['main_cloud_name'].value)) {
              return 0;
            } else {
              return 1;  
            }
          """,
        "params" : {
          "main" : "Azure"
        }
      },
      "order" : "ASC"
    }
  }
}
```

```json
GET /mytest/_search?pretty
{
  "query": { 
    "bool" : {
      "should" : [
        {
          "term" : {
            "main_cloud_name" : "AWS"
          }
        },
        {
          "term" : {
            "sub_cloud_name" : "AWS"
          }
        }
      ]
    }
  },
  "sort" : {
    "_script" : {
      "type" : "number",
      "script" : {
        "lang": "painless",
        "source": """
            if (params.main == (doc['main_cloud_name'].value)) {
              return 0;
            } else {
              return 1;  
            }
          """,
        "params" : {
          "main" : "AWS"
        }
      },
      "order" : "ASC"
    }
  }
}
```

```json
GET /mytest/_search?pretty
{
  "query": { 
    "bool" : {
      "should" : [
        {
          "term" : {
            "main_cloud_name" : "GCP"
          }
        },
        {
          "term" : {
            "sub_cloud_name" : "GCP"
          }
        }
      ]
    }
  },
  "sort" : {
    "_script" : {
      "type" : "number",
      "script" : {
        "lang": "painless",
        "source": """
            if (params.main == (doc['main_cloud_name'].value)) {
              return 0;
            } else {
              return 1;  
            }
          """,
        "params" : {
          "main" : "GCP"
        }
      },
      "order" : "ASC"
    }
  }
}
```

# Reference
- [Sort context | Painless Scripting Language7.6](https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-sort-context.html)
- [How to use scripts | Elasticsearch Reference7.6](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-using.html)
- [ElasticSearch Scripting: check if array contains a value](https://stackoverflow.com/questions/22566079/elasticsearch-scripting-check-if-array-contains-a-value)
- [Painless scripting language](https://www.elastic.co/guide/en/elasticsearch/reference/master/modules-scripting-painless.html)