CODEWITHSUNDEEP
pythonsortingelasticsearchsqlite
Jasmine Oliveira
Jasmine Oliveira

Reputation: 21

"Order By" Elasticsearch

I am recreating a database in sqlite3 into one in elasticsearch..

In a python script I had a line that was ordering the sqlite3 database by states like such, 

`rows = cur.execute("""SELECT * FROM {tn} ORDER BY (CASE state when 'CRITICAL' THEN 1 WHEN 'WARNING' then 2 WHEN 'UNKNOWN' THEN 3 ELSE 100 END) ASC;""".format(tn=table_name))`

I am wondering if there is a way, and if so how to replicate this "ORDER BY CASE" with the elasticsearch-python client?

Using the elasticsearch_dsl search object, I have this so far...

    ##Query for ID ##
    s = Search( using=client, index="logstash-*", )\
    .query("match", host="{h}".format(h=host_name))

    ## Sort by State ##
    s = s.sort("state", {"order" : "CRITICAL", "WARNING", "UNKNOWN"})

Thank you!!

Upvotes: 1

Views: 1419

Answers (1)

dlebech
dlebech

Reputation: 1839

Since you are re-creating your data, I would suggest taking the opportunity to remodel it a bit as well. This will be the easiest option. For example, storing the states as integers in your data instead of or in addition to strings. You could have an extra field state_num that contains the integer value and then map 'CRITICAL' to 1, 'WARNING' to 2, etc. It will be easy to sort by this field:

"sort" : [
  { "state_num" : "asc" }
]

If you don't want to remodel your data, another option is to use script-based sorting. For example:

"sort" : {
  "_script" : {
    "script" : "switch(doc['state'].value) { case 'CRITICAL': return 1; case 'WARNING': return 2; case 'UNKNOWN': return 3; default: return 100; }",
  "type" : "number",
  "order" : "asc"
  }
}

Upvotes: 2

Related Questions