Where Condition in _SQL

Elastic Stack Elasticsearch
1

Hello Team

I am using Elasticsearch version 7.8.0

I am in need of _SQL ...

curl -X POST "localhost:9200/_sql?format=txt&pretty" -H 'Content-Type: application/json' -d'
{
  "query": "SELECT count(*) FROM \"my-index-000001\" where \"Status\" = 'N' "
}
' -u user:pass

This Where condition ='N' is not working ...

I tried multiple things like using "" but its not helping me ...

Could someone please help me ... how to mention same in curl command.

2

Try with n instead of N.
What's the mapping for this field?

3

Hello @dadoonet

        "Status" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },

Same Error Message :

{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem\nline 1:57: Unknown column [n]"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem\nline 1:57: Unknown column [n]"
  },
  "status" : 400
}
4

What does this mean? No returned results? An error message?

I suspect the single quoting you're using for curl (-d') is interacting with single quoting within the query ('N'). Otherwise, you could leave out the double quoting around Status.

Either switch to using double quotes for curl, or use unicode escape for the single quotes (like Status=\u0027N\u0027).
In the documentation examples there are Copy as curl links you can find inspiration in.

5

Thank You @bogdan.pintea

Example Page did helped me to understand ..

curl -X POST "localhost:9200/_sql?format=txt&pretty" -H 'Content-Type: application/json' -d'
{
  "query": "SELECT count(*) FROM \"my-index-000001\" where Status = \u0027N\u0027 "
}
'

This worked correctly now ...

6

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