Is it possible to using _id in elasticsearch sql

Elastic Stack Elasticsearch
1

When i use _id in a elasticsearch sql，

GET /_sql
{
  "query": """
  select * from question where _id='RzSIwnABg0jflGUtAmBx'
  """
}

i get an error.

{
  "error": {
    "root_cause": [
      {
        "type": "verification_exception",
        "reason": "Found 1 problem\nline 2:32: Unknown column [_id]"
      }
    ],
    "type": "verification_exception",
    "reason": "Found 1 problem\nline 2:32: Unknown column [_id]"
  },
  "status": 400
}

Anyone can help？Thanks.

2

Hi @jimmy0

It seems to me that it is not possible to use _id because it is not a column.

Look this answer: Is there a way to access internal _id and _type fields in ES SQL query (XPack)? - #2 by Andrei_Stefan

3

Note that it's possible though using ES|QL instead:

FROM question METADATA _id
| WHERE _id == "RzSIwnABg0jflGUtAmBx"
| LIMIT 1
4

Thanks.
Then I tried

POST /_sql
{
  "query": "SELECT * from question METADATA _id WHERE _id == 'RzSIwnABg0jflGUtAmBx' LIMIT 1"
}

I got the error:

image
image1524×350 11.2 KB

5

ES|QL is not SQL.