Where Condition in _SQL

[tusharnemade]

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.

[dadoonet (David Pilato)]

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

[tusharnemade]

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
}

[bogdan.pintea]

@tusharnemade

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

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.

[tusharnemade]

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 ...