Elasticsearch 7.17 的 SQL 到底能查到什么？一次踩坑记录

AI 生成声明：本文内容由 AI 生成或经 AI 辅助完成，请注意甄别。

起因：ES 7.17 支持 SQL 吗

支持。Elasticsearch 从 6.3 开始提供 SQL 能力，7.17 作为 7.x 的收尾版本已经相当成熟，提供三种使用方式：

• REST 接口：POST _sql，默认返回 JSON（columns + rows），加 ?format=txt 返回文本表格；
• JDBC 驱动：Java 应用按标准 JDBC 方式连接；
• CLI：elasticsearch-sql-cli 在终端里直接跑 SQL。

SQL 在内部会被翻译成 Query DSL 执行，因此性能与手写 DSL 基本一致。看起来只要会写 SQL 就够了，但真到具体索引上，限制一个接一个冒出来。

坑一：数组（多值）字段不能出现在 SELECT 里

接下来查询 SELECT *，报错：

ql_illegal_argument_exception: Arrays (returned by [toc_path]) are not supported

原因很直接：toc_path 在映射里是多值字段（keyword 数组），而 SQL 是二维表模型，一个格子放不下数组，引擎干脆拒绝返回。

对应地，page_nos 这类数组字段也有同样问题。此时的可行做法有三种：

1. 显式列出标量字段，绕开数组列；
2. 用 UNNEST（7.7+ 支持）把数组展开成多行：

SELECT para_no, t.title
FROM demo_index
CROSS JOIN UNNEST(toc_path) AS t(title)
WHERE para_no > 30

3. 直接换 DSL。

至于把数组 CAST 成字符串，在 ES SQL 里基本走不通，不用浪费时间。

坑二：没有 SELECT * EXCLUDE

既然数组字段会阻塞 SELECT *，自然会想：能不能写 SELECT * EXCLUDE (toc_path)？

7.17 不行。EXCLUDE 是 8.x 才加入的语法，7.x 里没有任何“跳过某列”的写法。也就是说，想用 * 又同时有数组字段，SQL 这条路是死的，只能手写字段清单。

而手写清单的维护成本不低：索引每加一个字段，查询语句就得跟着改。

坑三：_id 查不到

手动列字段时，想把 _id 也带出来定位文档，结果：

verification_exception: Unknown column [_id]

试了几种写法都不行：

SELECT _id, doc_name FROM demo_index
SELECT "demo_index"._id, doc_name FROM demo_index

结论是：元数据字段 _id 在 ES SQL 中不保证可访问，取决于版本与索引映射配置。7.17 下这次是彻底不可见的。另外 field_multi_value_leniency 这个参数解决的是多值字段的宽松解析，跟 _id 无关，别被它误导。

SQL 与 DSL 返回结果的差异

走到这里，三个障碍已经很清楚了。再看两种方式返回结构的差别：

维度	SQL（_sql）	DSL（_search）
返回结构	扁平表：columns + rows	完整文档：_index / _id / _score / _source
元数据字段	_id、_score 不保证可查	始终返回
数组字段	不支持返回，直接报错	原样返回
嵌套对象	默认不支持	原样返回
全字段查询	SELECT * 遇数组字段即失败	_source 天然返回全部字段，即“SELECT *”
聚合	COUNT / SUM 等 SQL 函数	aggs 支持嵌套、管道、桶聚合
全文检索	MATCH() / QUERY() 函数	match、multi_match、query_string 等
分页	LIMIT / OFFSET	from / size / search_after
高亮	不支持	支持 highlight

同样是“查 para_no > 30 的文档”，DSL 只需要：

GET demo_index/_search
{
  "query": {
    "range": { "para_no": { "gt": 30 } }
  }
}

返回里自带 _id，_source 里包含全部字段，toc_path、page_nos 这样的数组也完整保留——这其实就是 SQL 里想要却拿不到的 SELECT *。

JSON 格式的 SQL 仍然只是表格

顺带说一个容易混淆的点：POST _sql 不加 ?format=txt 时返回的是 JSON，但结构仍然是表格：

{
  "columns": [{"name": "doc_name", "type": "text"}, {"name": "para_no", "type": "integer"}],
  "rows": [["示例文档.pdf", 538]],
  "cursor": "...",
  "total": 1
}

它适合程序解析，但不可能变成 DSL 那种 _source 嵌套结构——这与 format 取什么值无关，是 _sql API 的固有设计。

如果一定要“用 SQL 拿 DSL 格式”

有个折中方案：用 _sql/translate 把 SQL 翻译成 DSL，再执行翻译结果。

POST _sql/translate
{
  "query": "SELECT doc_name, para_no FROM demo_index WHERE doc_name LIKE '%示例%'"
}

返回：

{
  "size": 1000,
  "query": { "bool": { "filter": [ { "match": { "doc_name": "示例" } } ] } },
  "_source": { "includes": ["doc_name", "para_no"] }
}

把这段直接丢给 _search，就能拿到带 _id 的完整结构。不过既然都要执行 DSL 了，多这一步的价值有限，最多是当作“SQL 转 DSL 的翻译器”用。

小结

在 ES 7.17 上，SQL 适合做纯标量字段的简单查询与聚合，例如列几个字段做过滤、GROUP BY 统计。一旦索引里有多值字段、嵌套对象，或者需要 _id、_score、高亮，就会撞墙：

1. 数组字段不能出现在 SELECT 中；
2. 7.x 没有 SELECT * EXCLUDE，无法跳过数组列；
3. _id 等元数据字段不保证可访问；
4. 返回值永远是表格，无法还原文档层级。

所以判断标准很简单：要“文档”就用 DSL，要“报表”才用 SQL。想拿全部字段的原始文档，GET /<index>/_search 就是原生的 SELECT *，没有上面任何一个限制。

注：文中索引名、字段名、路径等均已做匿名化处理。