CODEWITHSUNDEEP
mysqlsqlsearchelasticsearchlucene
Edward Snowden
Edward Snowden

Reputation: 155

Elastic Search SQL Equivalent

Im having some problems querying with elasticsearch. So basically i wanted to know the sql equivalent of elasticsearch.

Whats the equivalent elasticsearch syntax for this sql query ?

SELECT * 
FROM   people 
WHERE  name LIKE( $name% ) 
       AND ( gender = 0 OR age < 18 ) 
       AND id IN( 1, 2, 3 ) 
       AND id NOT IN( 4, 5, 6 ) 
       AND dead = 0 
ORDER  BY status desc, 
          TIME desc

* is just for example

Upvotes: 2

Views: 1434

Answers (1)

Artholl
Artholl

Reputation: 93

Using boolean query (and nested boolean query ) allows you to express same things than sql request. Terms query is validated when one of its elements is found (OR).

POST /index_name/people/_search

{
   "query": {
      "bool": {
         "must": [
            {
               "match": {
                  "name": "John"
               }
            }
         ],
         "filter": [
            {
               "bool": {
                  "must": [
                     {
                        "terms": {
                           "id": [
                              1,
                              2,
                              3
                           ]
                        }
                     },
                     {
                        "term": {
                           "dead": 0
                        }
                     },
                     {
                        "bool": {
                           "should": [
                              {
                                 "term": {
                                    "gender": 0
                                 }
                              },
                              {
                                 "range": {
                                    "age": {
                                       "lt": 18
                                    }
                                 }
                              }
                           ]
                        }
                     }
                  ],
                  "must_not": [
                     {
                        "terms": {
                           "id": [
                              4,
                              5,
                              6
                           ]
                        }
                     }
                  ]
               }
            }
         ]
      }
   },
   "sort": [
      {
         "status": {
            "order": "desc"
         },
         "time": {
            "order": "desc"
         }
      }
   ]
}

Upvotes: 1

Related Questions