Thursday, January 1, 2015

ElasticSearch: query with AND and OR criteria

Frequently when we will need to construct query to elasticsearch which bear AND and OR criteria similar to the following SQL statements:

Statement 1:
SELECT * FROM tblOrder WHERE orderDate='2015-01-01 14:00:00' AND customerID=29439;

Statement 2:
SELECT * FROM tblOrder WHERE orderDate='2015-01-01 14:00:00' OR customerID=29439;

Statement 3:
SELECT * FROM tblOrder WHERE orderDate <= '2015-01-01 14:00:00' AND customerID=29439;

Statement 4:
SELECT * FROM tblOrder WHERE (orderDate='2015-01-01 14:00:00' AND customerID=29439) OR customerID = 20991;

Statement 5:
SELECT * FROM tblOrder WHERE orderDate='2015-01-01 14:00:00' AND (customerID=29439 OR customerID = 20991);

In ElasticSearch, we use "must" and "should" in place of AND and OR and "bool" in place of WHERE.

Suppose in our ElasticSearch (at 179.168.0.1:9200), we have indexed documents having the following structures at index myOrder/myOrder:

{
"orderID" : xxxxx,
"customerID"  : xxxxx,
"orderDate" : "yyyyMMdd'T'HHmmss",
"itemLines" [
  {
   "itemLineID" : xxxx,
   "productID" : yyyyy,
   "quantity" : xxxxx
  },
  ...
]
}

Below is the translation of the above SQL statements to equivalent ElasticSearch query, in the following example we use curl, and we want to get a max of 100 records in each query from start, i.e., 0):

Statement 1:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'
{
"from" : 0,
"size" : 100,
"query": {
   "bool" : {
       "must" : [
          "match" : {"orderDate" : "20150101T140000" },
          "match" : {"customerID" : 29439 }
       ]
   }
}
}'

Statement 2:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'
{
"from" : 0,
"size" : 100,
"query": {
   "bool" : {
       "should" : [
          "match" : {"orderDate" : "20150101T140000" },
          "match" : {"customerID" : 29439 }
       ]
   }
}
}'

Statement 3:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'
{
"from" : 0,
"size" : 100,
"query": {
   "bool" : {
       "must" : [
          "range" : {"orderDate" : { "lte" : "20150101T140000" } },
          "match" : {"customerID" : 29439 }
       ]
   }
}
}'

Statement 4:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'
{
"from" : 0,
"size" : 100,
"query": {
   "bool" : {
       "should" : [
          "bool" : {
              "must" : [
                 "match" : {"orderDate" : "20150101T140000" },
                 "match" : {"customerID" : 29439 }
              ]
          },
          "match" : { "customerID " :  20991}
       ]
   }
}
}'

Statement 5:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d'
{
"from" : 0,
"size" : 100,
"query": {
   "bool" : {
       "must" : [
          "match" : {"orderDate" : "20150101T140000" },
          "bool" : {
              "should" : [
                   "match" : { "customerID " :  20991}, 
                   "match" : {"customerID" : 29439 }
              ]
          }
       ]
   }
}
}'

No comments:

Post a Comment