SQL Query errors

Elastic Stack Elasticsearch
1

Hi there, I have an index called "index-1". And l wanna to do a WHERE query, my sql query is as follow:

curl -X POST "xxx/_xpack/sql" -H 'Content-Type: application/json' -d "{\"query\": \"SELECT * FROM 'index-1' where id='x-1'\"}"

and l also tried

curl -X POST "xxx/_xpack/sql" -H 'Content-Type: application/json' -d '{"query": "SELECT * FROM \"index-1\" where id=x-1"}'

it seems that both of them are not correct, Is there anyone can help? Thanks in advance!

2

Hi @tangl.

i don't know what exactly you are implementing but i use like below for sql query-

POST:-
http://localhost:9200/_sql?format=txt

{
"query": "SELECT * FROM dbdata where name='rahim' and id=101"
}

POST:-
http://localhost:9200/_sql?format=txt
{
"query": "SELECT * FROM dbdataindex_07_08_2019_demo1 where id=101 "
}

Thanks
HadoopHelp

3

@tangl

If you are trying to use 2 or more fields in the equality expression in the where clause, then this is semantically translated to a self join which is not supported.

4

no, l am using only 1 field in equality expression. The problem is both of the index name and id name has '-' which need to be escaped...but l don't find a way to do that...

5 
curl -X POST "xxx/_xpack/sql" -H 'Content-Type: application/json' -d '{"query": "SELECT * FROM "index-1" where id='"'"'x-1'"'"'"}'
6

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