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