Nowadays, almost everybody is used to work with some SQL queries in a daily basis, but what happens when the data store changes to Elasticsearch? You can find here some useful tips (I hope they are) to ease the transition.
For the Elasticsearch version, we will use single urls, bearing in mind that you are exposing Elasticsearch on the port 9200. In these examples we will use this User model:
groovy:
| Description | MySQL | ElasticSearch |
|---|---|---|
| Select by a field | SELECT * FROM user WHERE id = ‘1’; | http://localhost:9200/user/_search?q=id:1 |
| Select by a field | SELECT * FROM user WHERE name = ‘Fran’; | http://localhost:9200/user/_search?q=name:Fran |
| Select only some fields | SELECT id FROM user WHERE name = ‘Fran’; | http://localhost:9200/user/_search?q=name:Fran&_source=id |
| Select only some records | SELECT id FROM user WHERE department = ‘Engineering’ limit 0,50; | http://localhost:9200/user/_search?q=department:Engineering&size=50 |
| Count by a field | SELECT count(*) as total FROM user WHERE name=‘Fran’; | http://localhost:9200/user/_count?q=name:Fran |
| Deleting | DELETE FROM user WHERE id =‘1’; | curl -XPOST ‘http://localhost:9200/userindex/_delete_by_query’ —data ‘{
“query”: {
“match”: {
“id”: “1”
}
} }’ |
| Updating | UPDATE user SET name=‘Frank’ WHERE name=‘Fran’; | curl -XPOST ‘localhost:9200/user/_update_by_query?q=name:Fran’ -H ‘Content-Type: application/json’ —data ‘{“script”:{“source”:“ctx._source.name=Frank”,“lang”:“painless”}} |
| Grouping | SELECT count(*) FROM user GROUP BY department | curl -XGET ‘localhost:9200/user/_search?pretty’ -H ‘Content-Type: application/json’ -d’ { “size”: 0, “aggs”: { “group_by_department”: { “terms”: { “field”: “department” } } } } ‘ |
