Is there a way to access internal _id and _type fields in ES SQL query (XPack)?

[j_bennet (Irina Truong)]

It looks like currently (6.7), I can't select or filter on internal _id or _type fields in SQL, even though this is still possible with a regular JSON query. For example, this will not work:

POST /_xpack/sql?format=txt
{
    "query": """
    select *
    from "my_index_alias"
    where "_id" = "some-unique-doc-id"
    """
}

Will this functionality be available in later versions?

[Andrei_Stefan]

Hi @j_bennet,

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.

For _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".