SQL Query in Elasticsearch

Question

Hey I'm new to Elasticsearch and want to "translate" this SQL query :

SELECT"*" FROM Results WHERE "Id"=2 AND Number IN (25,27, 29) AND Date BETWEEN Date1 AND Date 2 ORDER BY Date LIMIT 20

EDIT: By "translate" I mean i want to convert the SQL statement to an elaticsearch query. I tried that but the elasticsearch query below isn't working yet :(

I wanted to do this using filters and I've got so far:

{
"filtered": {
    "query": {
        "match_all": {}
    },
    "filter": {
        "and": [
            {
                "range": {
                    "date": {
                        "gt": "2008-01-01",
                        "lt": "2014-01-01"
                    }
                }
            },
            {
                "term": {
                    "Id": 2
                }
            },
            {
                "terms": {
                    "number": [
                        25,
                        27,
                        29
                    ]
                }
            },
            {
             "limit": {
                    "value": 20
                }
            }
        ]
    }
}

}

I read the docs and tried examples, made simple queries, I hope somebody can help me with this!

Note: you also can check if your SQL query runs directly with your Elasticsearch instance, since the Elasticsearch 6.3 release, now (June 2018) comes with native SQL support. — VonC
– VonC, Commented Jun 16, 2018 at 16:15

Olly Cruickshank · Accepted Answer · 2014-11-16 10:05:25Z

1

Your search looks good - which part isn't working?

I would use size to limit the number of results returned (instead of limit which limits the number of documents checked). I've also added a sort.

The drawback with using filter is that it doesn't do any scoring - possibly you should move the Number IN (25,27, 29) section to a query and then results with more matches will score higher? The other fields look suitable for a filter though (i.e. a binary match / didn't match).

curl -XGET 'http://localhost:9200/myindex/results/_search?pretty' -d '{
   "size" : 20,
   "sort" : [{"number" : {"order" : "desc"}}],
   "filter": {
      "and": [
        {
            "range": {
                "date": {
                    "gt": "2008-01-01",
                    "lt": "2014-01-01"
                }
            }
        },
        {
            "term": {
                "Id": 2
            }
        },
        {
            "terms": {
                "number": [
                    25,
                    27,
                    29
                ]
            }
        }
    ]
  }
}'

answered Nov 16, 2014 at 10:05

Olly Cruickshank

6,2094 gold badges36 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Any1 Over a year ago

With your help it kinda works now, when I remove the "date" in my sort part..otherwise I get [No mapping found for [date] in order to sort on] I sorted on DATE instaed of NUMBER

Olly Cruickshank Over a year ago

Glad it's working for you, can you accept one of the answers?

Collectives™ on Stack Overflow

SQL Query in Elasticsearch

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related