Indeed, this is not possible.
The reason is that in SQL, an user is able to search for columns (fields) that they know about and that they actually inserted in Elasticsearch. But the
_id and the
_type are not fields/columns that you actually put in an index, this is more like meta information. And we bypass these fields in ES SQL by default.
_id we have a github issue created, but it's not on a concrete roadmap at the moment.
Also, if I may, I'd like to comment on a statement you made in your post -
even though this is still possible with a regular JSON query: the aim of ES SQL is not to mirror the Elasticsearch functionality but with SQL commands. ES SQL is a subset of standard SQL functionality for Elasticsearch. The thinking around ES SQL starts from SQL side, not from Elasticsearch side: "implementing SQL functionality in Elasticsearch, and not vice versa".