One thing that made me scared of Elastic was the way we have to create its queries. It is a completely new way of fetching, sorting and aggregating data. It uses what it calls ElasticSearch DSL Search. I was getting used to it until I got issues and couldn’t easily create AND’s and OR’s using this new language. After some non related Google search I’ve found what I should’ve found way, way earlier.

How to build a OR condition in Elasticsearch Query DSL

This website shows exactly what you need to do to create your own DSL queries, but the magical thing that got my attention was the built-in ElasticSearch SQL translator where you can send it a SQL Query such as this:

POST _sql/translate
{
  "query": "SELECT first_name FROM users WHERE (MATCH(first_name, 'frank') OR MATCH(first_name, 'damien')) AND age < 100",
  "fetch_size": 10
}

And it returns back DSL like this:

{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "match": {
                  "first_name": {
                    "query": "frank"
                  }
                }
              },
              {
                "match": {
                  "first_name": {
                    "query": "damien"
                  }
                }
              }
            ],
            "boost": 1
          }
        },
        {
          "range": {
            "age": {
              "lt": 100,
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "first_name"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

Since I have way more familiarity with SQL, I can make more powerful DSL queries from this method and still enjoy the quick searching algorithm from ElasticSearch.

This was magical to me.