# Mysql to elasticsearch Query conversion

**URL:** <https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381>\
**Category:** Elasticsearch\
**Created:** [September 16, 2015, 10:57am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381 "2015-09-16T10:57:58Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![rabeetwaqar](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@rabeetwaqar](https://discuss.elastic.co/u/rabeetwaqar)\
**Post date:** [September 16, 2015, 10:57am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/1 "2015-09-16T10:57:58Z")

</div>

SELECT user\_country, game\_name, proxy\_country, game\_server\_country, proxy\_id, COUNT(user\_country) AS total\_sessions,  
SUM(if(t1.avg\_ping \< t1.avg\_line\_ping, 1, 0)) as positive\_sessions, SUM(if(t1.avg\_ping \> t1.avg\_line\_ping, 1, 0)) as negative\_sessions  
FROM my\_table t1  
WHERE 1=1 AND session\_time \> 1441652400 AND session\_time \< 1442257200 GROUP BY user\_country, game\_name, proxy\_country, game\_server\_country ORDER BY total\_sessions DESC

@polyfractal  
Please any one help me out with this.  
Thanks

---

<div class="post-metadata">

**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\
**Post date:** [September 16, 2015, 1:45pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/2 "2015-09-16T13:45:07Z")

</div>

My SQL is rusty, but this is a fairly close approximation:

```json
{
    "query": {
        "filtered": {
            "filter": {
                "range": {
                    "session_time": {
                        "gte": 1441652400,
                        "lte": 1442257200
                    }
                }
            }
        }   
    },
    "aggs": {
        "user_country_agg": {
            "terms": {
                "field": "user_country"
            },
            "aggs": {
                "game_name_agg": {
                    "terms": {
                        "field": "game_name"
                    },
                    "aggs": {
                        "proxy_country": {
                            "terms": {
                                "field": "proxy_country"
                            },
                            "aggs": {
                                "game_server_country_agg": {
                                    "terms": {
                                        "field": "game_server_country"
                                    },
                                    "aggs": {
                                        "total_sessions": {
                                            "value_count": {
                                                "field": "user_country"
                                            }
                                        },
                                        "positive_sessions": {
                                            "sum": {
                                                "script" : "doc['avg_ping'].value < avg_line_ping ? 1 : 0"
                                            }
                                        },
                                        "negative_sessions": {
                                            "sum": {
                                                "script" : "doc['avg_ping'].value > avg_line_ping ? 1 : 0"
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        
    }
}

```

Note that it doesn't perform sorting, so if that is critical you may need a different solution. Currently, ES doesn't let you sort a `terms` agg based on the value of a multi-valued child agg (e.g. an embedded `terms`).

Also note that deeply embedded `terms` aggs can quickly cause problems due to high branching factor. E.g. if each `terms` agg has 50 terms, and you have four levels, that is `50^4 = 6,250,000` buckets. You may need to [use a different `collect_mode` to prevent memory problems](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#_collect_mode)

If sorting is critical, you could try something like this instead:

```json
{
    "query": {
        "filtered": {
            "filter": {
                "range": {
                    "session_time": {
                        "gte": 1441652400,
                        "lte": 1442257200
                    }
                }
            }
        }   
    },
    "aggs": {
        "user_country_agg": {
            "terms": {
                "script" : "doc['user_country'].value + '_' + doc['game_name_agg'].value + '_' + doc['proxy_country'].value + '_' + doc['game_server_country_agg'].value",
                "order": {
                    "total_sessions": "desc"
                }
            },
            "aggs": {
                "total_sessions": {
                    "value_count": {
                        "field": "user_country"
                    }
                },
                "positive_sessions": {
                    "sum": {
                        "script" : "doc['avg_ping'].value < avg_line_ping ? 1 : 0"
                    }
                },
                "negative_sessions": {
                    "sum": {
                        "script" : "doc['avg_ping'].value > avg_line_ping ? 1 : 0"
                    }
                }
            }
        }      
    }
}

```

E.g instead of nesting aggregations, you build your grouping key with a script by concatenating all the values together. Then you can sort on `total_sessions` since it is a single-valued agg

As general advice, directly translating SQL to Elasticsearch is not always the best idea. They operate on different principles, so you may have a hard time or bad performance trying to treat ES as a relational store.

---

<div class="post-metadata">

**Author:** ![rabeetwaqar](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@rabeetwaqar](https://discuss.elastic.co/u/rabeetwaqar)\
**Post date:** [September 16, 2015, 2:05pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/3 "2015-09-16T14:05:34Z")

</div>

Thanks alot 😄  
I almost made up the same query  
Thumbs up (y)

---

<div class="post-metadata">

**Author:** ![rabeetwaqar](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@rabeetwaqar](https://discuss.elastic.co/u/rabeetwaqar)\
**Post date:** [September 17, 2015, 7:36am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/4 "2015-09-17T07:36:01Z")

</div>

I have a question,  
How will i use the second sort query with the first one?

Thanks

---

<div class="post-metadata">

**Author:** ![rabeetwaqar](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@rabeetwaqar](https://discuss.elastic.co/u/rabeetwaqar)\
**Post date:** [September 17, 2015, 8:47am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/5 "2015-09-17T08:47:50Z")

</div>

One more question :

I want to SELECT proxy\_id in elasticsearch query also. Don't want to group\_by with proxy\_id.

How will i do this?

Thanks

---

<div class="post-metadata">

**Author:** ![rabeetwaqar](https://avatars.discourse-cdn.com/v4/letter/r/b2d939/32.png) [@rabeetwaqar](https://discuss.elastic.co/u/rabeetwaqar)\
**Post date:** [September 17, 2015, 10:30am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/6 "2015-09-17T10:30:36Z")

</div>

Unfortunately date range is not working , my final query is this.

{  
"size": 0,  
"query": {  
"filtered": {  
"query": {  
"query\_string": {  
"query": "\*",  
"analyze\_wildcard": true  
}  
},  
"filter": {  
"bool": {  
"must": [  
{  
"range": {  
"session\_time": {  
"gte": 1441652400,  
"lte": 1442257200  
}  
}  
}  
],  
"must\_not": []  
}  
}  
}  
},  
"aggs": {  
"date\_range": {  
"date\_histogram": {  
"field": "session\_time",  
"interval": "15m",  
"min\_doc\_count": 1,  
"extended\_bounds": {  
"min": 1441652400,  
"max": 1442257200  
}  
},  
"aggs": {  
"game\_name\_agg": {  
"terms": {  
"field": "game\_name"  
},  
"aggs": {  
"proxy\_country\_agg": {  
"terms": {  
"field": "proxy\_country"  
},  
"aggs": {  
"game\_server\_country\_agg": {  
"terms": {  
"field": "game\_server\_country"  
},  
"aggs": {  
"user\_country\_agg": {  
"terms": {  
"script": "doc['user\_country'].value + '_' + doc['game\_name'].value + '_' + doc['proxy\_country'].value + '\_' + doc['game\_server\_country'].value",  
"order": {  
"total\_sessions": "desc"  
}  
},  
"aggs": {  
"total\_sessions": {  
"value\_count": {  
"field": "user\_country"  
}  
},  
"positive\_sessions": {  
"sum": {  
"script": "doc['avg\_ping'].value \< doc['avg\_line\_ping'].value ? 1 : 0"  
}  
},  
"negative\_sessions": {  
"sum": {  
"script": "doc['avg\_ping'].value \> doc['avg\_line\_ping'].value ? 1 : 0"  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}

Thanks

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 3, 2016, 2:58am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/7 "2016-02-03T02:58:01Z")

</div>

Help me to convert this MySQL query to elasticsearch (json) query and how to set it on kibana.

SELECT  
user, call\_date, sum(call\_length\_in\_sec) as 'work\_time',  
(max(end\_epoch)-min(start\_epoch))-sum(length\_in\_sec) as 'idle\_time'  
FROM vicidial\_log  
WHERE date(call\_date)='2016-01-12'  
group by user

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [February 3, 2016, 7:21am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/8 "2016-02-03T07:21:29Z")

</div>

Please start a new thread for your question.

---

<div class="post-metadata">

**Author:** ![Croos](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/croos/32/29489_2.png) [@Croos](https://discuss.elastic.co/u/Croos)\
**Post date:** [February 3, 2016, 7:37am UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/9 "2016-02-03T07:37:04Z")

</div>

> [@Croos](#):
>
> Help me to convert this MySQL query to elasticsearch (json) query and how to set it on kibana.
> 
> SELECT user, call\_date, sum(call\_length\_in\_sec) as 'work\_time', (max(end\_epoch)-min(start\_epoch))-sum(length\_in\_sec) as 'idle\_time' FROM vicidial\_log WHERE date(call\_date)='2016-01-12' group by user

ok. Thanks for your response. I have posted the same as a new discussion. please refer it on the following link.

> [@Convert MySQL query to elasticsearch](https://discuss.elastic.co/t/convert-mysql-query-to-elasticsearch/40830):
>
> Help me to convert this MySQL query to elasticsearch (json) query and how to visualize it on kibana. SELECT user, call\_date, sum(length\_in\_sec) as 'work\_time', (max(end\_epoch)-min(start\_epoch))-sum(length\_in\_sec) as 'idle\_time' FROM vicidial\_log WHERE date(call\_date)='2016-01-12' group by user

---

<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:** [July 5, 2017, 11:19pm UTC](https://discuss.elastic.co/t/mysql-to-elasticsearch-query-conversion/29381/10 "2017-07-05T23:19:20Z")

</div>