# Sql access query not working via curl
**URL:** https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195
**Category:** Elasticsearch
**Tags:** elastic-stack-sql
**Created:** [January 23, 2020, 8:16am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195 "2020-01-23T08:16:58Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@vijay\_kaali](https://discuss.elastic.co/u/vijay_kaali)
#### Post date: [January 23, 2020, 8:16am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/1 "2020-01-23T08:16:58Z")
we have 6.5 elk . and in dev tools following query works fine
POST /\_xpack/sql?format=txt
{
"query": "select "@timestamp" from "mbeat-2020.01.23" where "@timestamp" \>CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}
but the same is throwing error via curl
[root ~]# curl -XGET "[http://localhost:9200/\_xpack/sql?format=txt](http://localhost:9200/_xpack/sql?format=txt)" -H 'Content-Type: application/json' -d'
> {
> "query": "select "@timestamp" from "mbeat-2020.01.23" where "@timestamp" \>CAST("2020-01-23T05:00:00Z" AS TIMESTAMP)"
> }'
> {"error":{"root\_cause":[{"type":"x\_content\_parse\_exception","reason":"[3:14] [sql/query] failed to parse object"}],"type":"x\_content\_parse\_exception","reason":"[3:14] [sql/query] failed to parse object","caused\_by":{"type":"json\_parse\_exception","reason":"Unexpected character ('2' (code 50)): was expecting comma to separate Object entries\n at [Source: org.elasticsearch.transport.netty4.ByteBufStreamInput@1789e39b; line: 3, column: 102]"}},"status":400}[root@ukwysms00235 ~]#
it is not accepting CAST("2020-01-23T05:00:00Z" ,
i tried with single quotes , escaping with \ on both single /double quotes . but none is working
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [January 23, 2020, 10:46am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/2 "2020-01-23T10:46:35Z")
This has worked for me
```auto
POST /_xpack/sql?format=txt
{
"query": "select \"@timestamp\" from \"foo\" where \"@timestamp\" >CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}
```
maybe you can share the examples that you tried. Keep in mind that you have to create valid JSON.
---
### Author:  [@vijay\_kaali](https://discuss.elastic.co/u/vijay_kaali)
#### Post date: [January 23, 2020, 11:16am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/3 "2020-01-23T11:16:04Z")
Hi As i said earlier is working in Dev tools kibana
POST /\_xpack/sql?format=txt
{
"query": "select "@timestamp" from "mbeat-2020.01.23" where "@timestamp" \>CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}
I get only when i run in via curl
---
### Author:  [@harshbajaj16](https://discuss.elastic.co/u/harshbajaj16)
#### Post date: [January 23, 2020, 11:33am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/4 "2020-01-23T11:33:38Z")
Hi @vijay_kaali
In dev tools you mentioned `POST` but if you see your curl request you are doing with `GET` method.
> [@vijay\_kaali](#):
>
> POST /\_xpack/sql?format=txt
..........
> [@vijay\_kaali](#):
>
> [root ~]# curl -XGET
Please use POST method in curl command also as below:
`curl -X POST "localhost:9200/_sql?format=txt&pretty" -H 'Content-Type: application/json' -d'`
Regards,
Harsh Bajaj
---
### Author:  [@vijay\_kaali](https://discuss.elastic.co/u/vijay_kaali)
#### Post date: [January 23, 2020, 12:24pm UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/5 "2020-01-23T12:24:34Z")
Post is not a problem . but in curl it is throwing error on cast timestamp literal value part
curl -XPOST "[http://localhost:9200/\_xpack/sql?format=txt](http://localhost:9200/_xpack/sql?format=txt)" -H 'Content-Type: application/json' -d'
> {
> "query": "select "@timestamp" from "metricbeat.2020.01.23" where "@timestamp" \>CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
> }'
> {"error":{"root\_cause":[{"type":"parsing\_exception","reason":"line 1:88: identifiers must not start with a digit; please use double quotes"}],"type":"parsing\_exception","reason":"line 1:88: identifiers must not start with a digit; please use double quotes"},"status":400}[root@ukwysms00235 ~]#
curl -XPOST "[http://localhost:9200/\_xpack/sql?format=txt](http://localhost:9200/_xpack/sql?format=txt)" -H 'Content-Type: application/json' -d'
> {
> "query": "select "@timestamp" from "metricbeat.2020.01.23" where "@timestamp" \>CAST("2020-01-23T05:00:00Z" AS TIMESTAMP)"}'
> {"error":{"root\_cause":[{"type":"verification\_exception","reason":"Found 1 problem(s)\nline 1:80: Unknown column [2020-01-23T05:00:00Z]"}],"type":"verification\_exception","reason":"Found 1 problem(s)\nline 1:80: Unknown column [2020-01-23T05:00:00Z]"},"status":400}[root@ukwysms00235 ~]#
if i remove full where it is working fine .
---
### Author:  [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)
#### Post date: [January 28, 2020, 3:57pm UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/6 "2020-01-28T15:57:25Z")
You need to escape the double quotes surrounding `@timestamp`. You look closely at this query - [Sql access query not working via curl](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/2) - there are backslashes there for the double quotes.
Also, `CAST("2020-01-23T05:00:00Z" AS TIMESTAMP)` should be used with single quotes: `CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)`.
Please, read here more about single quotes and double quotes in ES SQL: [https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-lexical-structure.html#sql-syntax-single-vs-double-quotes](https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-lexical-structure.html#sql-syntax-single-vs-double-quotes)
---
### Author:  [@vijay\_kaali](https://discuss.elastic.co/u/vijay_kaali)
#### Post date: [January 31, 2020, 12:32am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/8 "2020-01-31T00:32:18Z")
2020 has to be replaced by single quote,backslash,double single quote 2020
CAST('''2020-01-28T05:00:00Z''' . backslash is removed in reply formatter here .
another problem is if i do max(@timestamp ) then , it returns double value instead of timestamp format both in using sql /json output . how to correct it .
And is there any way to extract json value via shell script or make json section in single line
---
### Author:  [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)
#### Post date: [January 31, 2020, 9:33am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/9 "2020-01-31T09:33:58Z")
`max(date_field)` indeed used to return a double value. But this bug is fixed in more recent versions: 6.6.3 is the earliest one that has the fix. For more details and more versions where the fix has been applied, have a look here: [https://github.com/elastic/elasticsearch/pull/40377](https://github.com/elastic/elasticsearch/pull/40377).
For json output (and on a single line), use `format=json`: `/_xpack/sql?format=json`
---
### Author:  [@vijay\_kaali](https://discuss.elastic.co/u/vijay_kaali)
#### Post date: [January 31, 2020, 10:55am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/10 "2020-01-31T10:55:24Z")
Thanks a lot .
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [February 28, 2020, 10:55am UTC](https://discuss.elastic.co/t/sql-access-query-not-working-via-curl/216195/11 "2020-02-28T10:55:33Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.