MySQL - json_extract(), json_table() and json_search()

{"widges":[{"id": "aaa", "type": "type_a"}, {"id": "bbb", "type": "type_b"}, {"id": "ccc", "type": "type_c"}]}"

对于上面的json，如何用MySQL 的JSON_EXTRACT()函数SELECT出id="ccc"的type的值？

 

ChatGPT said:

在 MySQL 中，如果你有一个 JSON 字段（例如 data），内容如下：

{
  "widges": [
    {"id": "aaa", "type": "type_a"},
    {"id": "bbb", "type": "type_b"},
    {"id": "ccc", "type": "type_c"}
  ]
}

 

并且你想通过 JSON_EXTRACT() 查询出 id = "ccc" 的 type 值（即 type_c），
可以使用 MySQL 8.0 的 JSON_TABLE()（推荐）或纯 JSON_EXTRACT()+JSON_SEARCH() 的组合。

---

✅ 方法 1：使用 JSON_TABLE()（推荐，语义清晰）

SELECT jt.type
FROM your_table,
JSON_TABLE(
  data,
  '$.widges[*]'
  COLUMNS (
    id   VARCHAR(50) PATH '$.id',
    type VARCHAR(50) PATH '$.type'
  )
) AS jt
WHERE jt.id = 'ccc';

👉 输出结果：

type_c

---

✅ 方法 2：仅用 JSON_EXTRACT() + JSON_SEARCH()（兼容旧版本）

如果你的 MySQL 版本 < 8.0.4（不支持 JSON_TABLE），可以用：

SELECT JSON_EXTRACT(
  data,
  CONCAT('$.widges[', JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id')), '].type')
) AS type_value
FROM your_table;

但注意，这种写法较复杂，需要一些细节调整。

为了让它可运行，我们拆解一下逻辑：

完整可执行版本：

SELECT JSON_EXTRACT(
  data,
  REPLACE(
    JSON_UNQUOTE(
      JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id')
    ),
    '.id',
    '.type'
  )
) AS type_value
FROM your_table;

👉 输出结果：

"type_c"

---

✅ 总结

方法	适用版本	优点	示例结果
JSON_TABLE()	MySQL ≥ 8.0.4	简洁清晰	type_c
JSON_SEARCH() + JSON_EXTRACT()	MySQL ≥ 5.7	向下兼容	"type_c"（带引号）

 

在前面给出的这段代码中： 

SELECT jt.type
FROM your_table,
JSON_TABLE(
  data,
  '$.widges[*]'
  COLUMNS (
    id   VARCHAR(50) PATH '$.id',
    type VARCHAR(50) PATH '$.type'
  )
) AS jt
WHERE jt.id = 'ccc';

'$.widges[*]'和COLUMNS之间没有逗号分隔吗？

 

ChatGPT said:

非常好的观察 👏，这个细节确实容易让人疑惑。

在 MySQL 的 JSON_TABLE() 语法中，
'$[path expression]' 和 COLUMNS (...) 之间确实不需要逗号。
这是 MySQL 语法规范设计的结果。

---

✅ 正确语法结构

官方语法是这样的：

JSON_TABLE(
  json_doc,
  path_expression
  COLUMNS (
    column_name column_type PATH path_spec ...
  )
)

 

注意：

• json_doc（要解析的 JSON 列或表达式）和 path_expression 之间用逗号；

• 但 path_expression 和 COLUMNS (...) 之间没有逗号。

---

✅ 举例对比

正确 ✅：

SELECT *
FROM JSON_TABLE(
  data,
  '$.widges[*]'
  COLUMNS (
    id   VARCHAR(50) PATH '$.id',
    type VARCHAR(50) PATH '$.type'
  )
) AS jt;

 

错误 ❌（多了一个逗号，会报语法错误）：

SELECT *
FROM JSON_TABLE(
  data,
  '$.widges[*]',   -- ❌ 这里的逗号是错误的
  COLUMNS (
    id   VARCHAR(50) PATH '$.id',
    type VARCHAR(50) PATH '$.type'
  )
) AS jt;

 

---

🧩 小结

元素	是否用逗号分隔	说明
data 和 '$.widges[*]'	✅ 要逗号	参数分隔
'$.widges[*]' 和 COLUMNS (...)	❌ 不要逗号	属于一个结构

 

 

JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id') 上面json_search函数的参数'one'和NULL是什么意思？

 

ChatGPT said:

非常好，这两个参数在 JSON_SEARCH() 函数里确实很关键 👍
下面是详细解释：

---

✅ 函数原型

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

它在一个 JSON 文档中查找匹配指定字符串的位置，并返回匹配值的路径表达式。

---

✅ 参数详解

参数	含义	说明
json_doc	要搜索的 JSON 文档	比如：data
one_or_all	查找方式	'one' 或 'all'
search_str	要搜索的字符串	比如 'ccc'
escape_char	转义字符，可选	通常写 NULL（表示不用）
path	限定搜索的 JSON 路径，可选	比如 '$.widges[*].id'

---

🔍 参数 'one' 的作用

'one' 表示：

只返回第一个匹配项的路径。

示例：

JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id')

结果可能是：

"$.widges[2].id"

 

🔍 如果改成 'all'

'all' 表示：

返回所有匹配项路径的数组。

示例：

JSON_SEARCH(data, 'all', 'ccc', NULL, '$.widges[*].id')

结果可能是：

["$.widges[2].id"]

如果有多个匹配，会返回多个路径：

["$.widges[2].id", "$.widges[5].id"]

 

---

🔍 参数 NULL 的作用（escape_char）

这是“转义字符”的占位参数。
通常写 NULL，表示：

不需要使用转义字符。

它主要用于在 search_str 中包含 % 或 _（类似 LIKE 匹配）时使用。
但 JSON_SEARCH 默认执行精确匹配，所以一般都写 NULL。

例如：

JSON_SEARCH(data, 'one', 'ccc', NULL, '$.widges[*].id')

这里的 NULL 只是告诉 MySQL：

“没有特殊转义规则，按原样匹配 ccc。”

---

✅ 总结

参数	示例	含义
'one'	JSON_SEARCH(..., 'one', 'ccc', ...)	返回第一个匹配路径
'all'	JSON_SEARCH(..., 'all', 'ccc', ...)	返回所有匹配路径（数组）
NULL	第四个参数	不启用转义字符处理