# ElasticSearch Query from MySql Query

**URL:** <https://discuss.elastic.co/t/elasticsearch-query-from-mysql-query/106123>\
**Category:** Elasticsearch\
**Created:** [November 2, 2017, 5:31am UTC](https://discuss.elastic.co/t/elasticsearch-query-from-mysql-query/106123 "2017-11-02T05:31:49Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sagarch888](https://avatars.discourse-cdn.com/v4/letter/s/f4b2a3/32.png) [@sagarch888](https://discuss.elastic.co/u/sagarch888)\
**Post date:** [November 2, 2017, 5:31am UTC](https://discuss.elastic.co/t/elasticsearch-query-from-mysql-query/106123/1 "2017-11-02T05:31:50Z")

</div>

ElasticSearch Version: 5.6

I have imported MySQL data in ElasticSearch and I have added mapping to the elastic search as required. Following is one mapping for the column `application_status`.

Mappings:

```
{
"settings": {
    "analysis": {
        "analyzer": {
            "case_insensitive": {
                "type": "custom",
                "tokenizer": "keyword",
                "filter": ["lowercase"]
            }
        }
    }
},
"mappings": {
    "lead": {
        "properties": {
            "application_status": {
                "type": "string",
                "analyzer": "case_insensitive",
                "fields": {
                    "keyword": {
                        "type": "keyword"
                    }
                }
            }
        }
    }
}}

```

On the above mapping, I am able to do simple sorting (`asc` or `desc`) using following query:

```
{
"size": 50,
"from": 0,
"sort": [{
    "application_status.keyword": {
        "order": "asc"
    }
}]}

```

which is MySql equivalent of

```
select * from <table_name> order by application_status asc limit 50;

```

**Need help on following problem:**  
I have MySQL query which sorts based on `application_status`:

```
select * from vLoan_application_grid order by CASE WHEN application_status = "IP_QUAL_REASSI" THEN application_status END desc, CASE WHEN application_status = "IP_COMPLE" THEN application_status END desc, CASE WHEN application_status LIKE "IP_FRESH%" THEN application_status END desc, CASE WHEN application_status LIKE "IP_%" THEN application_status END desc

```

Please help me write the same query in ElasticSearch. I am not able to find `order by value` equivalent for `strings` in ElasticSearch. Searching online, I understood that, I should use `sorting scripts` but not able to find any proper documentation.

I have following query which just does simple sort.

```
{
"size": 500,
"from": 0,
"query" : {
    "match_all": {}
},
"sort": {
    "_script": {
        "type": "string",
        "script": {
            "source": "doc['application_status.keyword'].value",
            "params": {
                "factor": ["IP_QUAL_REASS", "IP_COMPLE"]
            }
        },
        "order": "desc"
    }
}}

```

In the above query, I am not using `params` section as I am not aware how to use it for `type: string`

I believe I am asking too much. Please help or any relevant documentation links would be greatly appreciated. Hope question is clear. I'll provide more details if necessary.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 30, 2017, 5:31am UTC](https://discuss.elastic.co/t/elasticsearch-query-from-mysql-query/106123/2 "2017-11-30T05:31:58Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.