Good Day,
We are moving to Elastic search and I am struggle with a elastic search query, I have my mysql query and want to search on where clause by date and service_id and then do sum and count and a group by
MySQL Query:
SELECT count(mtsms_id) as 'SENT', DATE(timestamp) as mttimestamp, sum(billing_units) as 'UNITS',
SUM(If ((dlr & 1 = 1), 1,0)) as 'DELIVERED',SUM(If ((dlr & 2 = 2), 1,0)) as 'FAILED',MCCMNC, SMSC
FROM data s
WHERE DATE(timestamp) = '2014-09-01'
and id=400
group by DATE(timestamp),mccmnc, smsc
Elastic
{
"from" : 0, "size" : 100,
"fields" : ["mtsms_id","id","timestamp","dlr","billing_units","mccmnc","smsc","rdnc","route_billing"],
"query" : {
"range": {
"dlr_timestamp": {
"from": "2014-09-01"
}
}
}
}
Document:
"_id": "360211261",
"_score": 1,
"_source": {
"mtsms_id": 360211261,
"id": 400,
"route_id": 3457,
"content": "Hello",
"timestamp": "2014-09-03T15:02:56+02:00",
"meta": {
"clid": "conejo:32859363",
"msg_id": 358528184,
"batch_identifier": "6064523",
"type": "mtsms",
"udhl": 0,
"udl": 151,
"udbytes": "132.125",
"mccmnc": "65501",
"ported": 0,
"parts": 1,
"credit": 1150,
"autotag_client_src": "54071176a1226",
"smsc": "ok_smsc",
"status": "DELIVERED"
},
"dlr": 681,
"dlr_timestamp": "2014-09-03T15:02:56+02:00",
"billing_units": 1,
"core_mtsms_answer": "stat:DELIVRD",
"mccmnc": "65501",
"smsc": "ok_smsc",
"rdnc": 0,
"route_billing": 2050