SQL WHERE clause usage

[Pradyumna_Achar]

Hello,
I am trying to understand basic usage of the WHERE clause in Elasicsearch SQL queries.
I loaded the shakespeare dataset from https://www.elastic.co/guide/en/kibana/current/tutorial-load-dataset.html

While I am able to use the WHERE clause on integer fields, I can't do the same with keyword fields, as shown below. What is the right usage of the WHERE clause for keyword fields?

Thank you.

root@bef0601e3d30:/#
root@bef0601e3d30:/# curl -d '{"query":"select * from shakespeare where speech_number=2 limit 5"}' -X POST -H 'Content-Type: application/json' elastic:9200/_xpack/sql?format=txt
    line_id    |  line_number  |   play_name   |    speaker    | speech_number |                 text_entry                  |     type
---------------+---------------+---------------+---------------+---------------+---------------------------------------------+---------------
38             |1.1.35         |Henry IV       |WESTMORELAND   |2              |And many limits of the charge set down       |line
40             |1.1.37         |Henry IV       |WESTMORELAND   |2              |A post from Wales loaden with heavy news;    |line
46             |1.1.43         |Henry IV       |WESTMORELAND   |2              |Upon whose dead corpse there was such misuse,|line
37             |1.1.34         |Henry IV       |WESTMORELAND   |2              |My liege, this haste was hot in question,    |line
47             |1.1.44         |Henry IV       |WESTMORELAND   |2              |Such beastly shameless transformation,       |line
root@bef0601e3d30:/# curl -d '{"query":"select * from shakespeare where speaker='WESTMORELAND' limit 5"}' -X POST -H 'Content-Type: application/json' elastic:9200/_xpack/sql?format=txt
{"error":{"root_cause":[{"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:41: Unknown column [WESTMORELAND]"}],"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:41: Unknown column [WESTMORELAND]"},"status":400}root@bef0601e3d30:/#
root@bef0601e3d30:/#

[Andrei_Stefan]

@Pradyumna_Achar I think you need to escape the single quotes surrounding the WESTMORELAND word, since your curl using the single quotes for the json block.

Or, alternatively, you can use curl with double quotes and escape all the double quotes inside your json: curl -d "{\"query\":\"select * from shakespeare where speaker='WESTMORELAND' limit 5\"}" -X POST -H 'Content-Type: application/json' elastic:9200/_xpack/sql?format=txt'

[Pradyumna_Achar]

Thank you, that worked.