[Solved] Using /_xpack/sql endpoint with dates with curl

Elastic Stack Elasticsearch
1

Hello,

This works good:

$ curl -X POST "localhost:9200/_xpack/sql?format=txt&pretty" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT count(*) FROM whatsapp_202010 WHERE clientInfo.customerId = 7503"
}
'
   count(*)    
---------------
241176

I'm having issues to use with timestamp:

  • Using "... AND requestDatetimes.routedAt >= "2020-10-19T00:00:00":

Error : "reason" : "Unexpected character ('2' (code 50)): was expecting comma to separate Object entries\n at [Source: org.elasticsearch.transport.netty4.ByteBufStreamInput@336a791e; line: 3, column: 122]"

  • Using "... AND requestDatetimes.routedAt >= '2020-10-19T00:00:00'"

Error: reason" : "line 1:114: identifiers must not start with a digit; please use double quotes"

  • Using "... AND requestDatetimes.routedAt >= \"2020-10-19T00:00:00\""

Error: "reason" : "Found 1 problem(s)\nline 1:106: Unknown column [2020-10-19T00:00:00]"

Can you help please?
Thanks!

PS: Using elasticsearch-sql-cli works good:

sql> SELECT count(*) FROM whatsapp_202010 WHERE clientInfo.customerId = 7503 AND requestDatetimes.routedAt >= '2020-10-19T00:00:00';
   count(*)    
---------------
36622
2

This will depend on the shell you use, but smth like the below should work:

$ curl -X POST "localhost:9200/_xpack/sql?format=txt&pretty" -H 'Content-Type: application/json' -d "{\"query\": \"SELECT count(*) FROM whatsapp_202010 WHERE clientInfo.customerId = 7503 AND requestDatetimes.routedAt >= '2020-10-19T00:00:00'\"}"
1 Like
3

Thanks bogdan!

Another way that I managed to use was:

$ curl -X POST "localhost:9200/_xpack/sql?format=txt&pretty" -H 'Content-Type: application/json' -d'
{
    "query": "SELECT count(*) FROM whatsapp_202010 WHERE clientInfo.customerId = 5366 AND requestDatetimes.routedAt >= CURDATE() - INTERVAL 30 DAY"
}
'
   count(*)    
---------------
123456

Thanks

4

Sure, and that's probably closer to what you wanted anyways, I guess.
For the record, single quoting as you initially wanted is possible too, but depending on your shell, you might need to use the "ansi c quoting", i.e. something like:

... -d $'
{
    "query": "SELE...  >= \'2020-10-19T00:00:00\'"
}'
1 Like
5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.