Error with _sql query with curl

[rodri.gz (RGZ-ELK)]

Hello !

I am trying to cast a query to my Elasticsearch cluster. When I use where with numeric fields, everything works correctly, but when I try to use it with text fields, problems appear.

What would be the correct syntax?

correct example:

curl -XPOST -u elastic:xxxxxxx -k https://yyyyyyyyyyyy:9200/_sql?format=csv -H "Content-Type: application/json" -d '
{
"query": "SELECT * FROM \"yyyyy-lllll-ultimo-dia\"  WHERE phone_tlf=1",
"fetch_size": 10000
}'

Error example:

curl -XPOST -u elastic:xxxxxxx -k https://yyyyyyyyyyyy:9200/_sql?format=csv -H "Content-Type: application/json" -d '
{
"query": "SELECT * FROM \"aaaa-bbb-last-day\"  WHERE employee-user=AB97C-2",
"fetch_size": 10000
}'

Could someone tell me where I have to put the " or, " or nothing?

It would be a great help.
Thanks in advance!

[bogdan.pintea]

@rodri.gz , you'll want to escape the string literal with single quotes: ...WHERE employee-user='AB97C-2'

[rodri.gz (RGZ-ELK)]

First of all thanks for the help.

Secondly, I have tried with the format that you mention and it detects the value as a column.

curl -XPOST -u xxx:yyyy-k https://zzzzzzzzzzz:9200/_sql?format=csv -H "Content-Type: application/json" -d '
 {
	"query": "SELECT * FROM \"abc\"  WHERE Provincia='Alicante'  ",
	"fetch_size": 10000
  }'

response:

{"error":{"root_cause":[{"type":"verification_exception","reason":"Found 1 problem\nline 1:56: Unknown column [Alicante]"}],"type":"verification_exception","reason":"Found 1 problem\nline 1:56: Unknown column [Alicante]"}

I have also tried with the format "field"='value' but it doesn't work either, any advice?

[bogdan.pintea]

You're passing the content of the query to curl with -d', so the next occurring ' (like those framing your literal) might interfere with the argument passing and not be transmitted as such to Elasticsearch. You might need to escape the inner simple quotes.

[rodri.gz (RGZ-ELK)]

i will try it! thank you