CSV option giving -Infinity and Nan

[CharanSahaj]

@shi-yuan There are cases when there is a datehistogram applied then elasticsearch gives null and if you try to aggregate on these then you get a Nan or -Infinity in the result. You can add a check for minimum doc count before you do a conversion into csv probably? Let me know if you need example data

[shi-yuan]

Thanks, could you please give me some data ?

[CharanSahaj]

@shi-yuan sure :)
curl -XPUT 'localhost:9200/kent.transaction.2017.02/events/1qeg157t-c24f-1234-8527-51e521215me6' -d'
{
"hd": {
"org": "marvel",
"ec": "transaction",
"es": "",
"et": "SecurityBuy",
"eid": "1qeg157t-c24f-1234-8527-51e521215me6",
"uid": "TonyStark@marvel.com",
"ts": "2017-02-20T19:20:30.45Z"
},
"pl": {
"price": 8,
"currency": "usd",
"date": "1998-07-16T19:20:30.45+01:00",
"item": {
"id": "id4",
"name": "Ark-Knight",
"type": "Movie"
}
},
"ex": {
"rts": 1510043481723,
"hl": {
"ml": {
"summary": "this is the summary",
"keywords": ["reviews", "paragraphs", "paragraph"],
"topics": {"topic1" : 0.5, "topic1" : 0.5},
"entities": ["reviews", "paragraphs", "paragraph"],
"sentiment": {"neg": 0.5 , "neu": 0.712 , "pos": 0.145 , "compound": 0.2344} ,
"intent": "this is the intent",
"classification": {"neg": 0.5, "neu": 0.32, "pos": 0.038, "compound": 0.18}
},
"hl": {
}
}
}
}'

curl -XPUT 'localhost:9200/kent.transaction.2017.04/events/1trg157t-c24f-1234-8527-51e521215me6' -d'
{
"hd": {
"org": "marvel",
"ec": "transaction",
"es": "",
"et": "SecurityBuy",
"eid": "1trg157t-c24f-1234-8527-51e521215me6",
"uid": "TonyStark@marvel.com",
"ts": "2017-04-20T19:20:30.45Z"
},
"pl": {
"price": 8,
"currency": "usd",
"date": "1998-07-16T19:20:30.45+01:00",
"item": {
"id": "id4",
"name": "Ark-Knight",
"type": "Movie"
}
},
"ex": {
"rts": 1510043481723,
"hl": {
"ml": {
"summary": "this is the summary",
"keywords": ["reviews", "paragraphs", "paragraph"],
"topics": {"topic1" : 0.5, "topic1" : 0.5},
"entities": ["reviews", "paragraphs", "paragraph"],
"sentiment": {"neg": 0.5 , "neu": 0.712 , "pos": 0.145 , "compound": 0.2344} ,
"intent": "this is the intent",
"classification": {"neg": 0.5, "neu": 0.32, "pos": 0.038, "compound": 0.18}
},
"hl": {
}
}
}
}'

localhost:9200/_sql?flat=true&format=csv&sql=SELECT+MAX%28pl.price%29+FROM+kent.transaction*+WHERE+hd.ts%3E%3D%272016-11-11T00%3A00%3A00%2B05%3A30%27+AND+hd.ts%3C%3D%272019-11-11T00%3A06%3A00%2B05%3A30%27++GROUP+BY+date_histogram%28field%3D%27hd.ts%27%2C%27interval%27%3D%27hour%27%29

you get the response
date_histogram(field=hd.ts,interval=hour),MAX(pl.price)
2017-02-20 19:00:00,8.0
2017-02-20 20:00:00,-Infinity
2017-02-20 21:00:00,-Infinity
2017-02-20 22:00:00,-Infinity
2017-02-20 23:00:00,-Infinity
2017-02-21 00:00:00,-Infinity
2017-02-21 01:00:00,-Infinity
2017-02-21 02:00:00,-Infinity
2017-02-21 03:00:00,-Infinity
2017-02-21 04:00:00,-Infinity
2017-02-21 05:00:00,-Infinity
2017-02-21 06:00:00,-Infinity
2017-02-21 07:00:00,-Infinity
.... if you give a avg then you get Nan. If not CSV it gives null in the json response from the plugin but csv is messed up.

[CharanSahaj]

@shi-yuan do you need anything else?

[shi-yuan]

Aha, Let me see !