MySQL 使用特定的 JSON 路径表达式语法来导航和提取 JSON 文档中的数据
MySQL 中的 JSON 路径遵循以下通用格式
$[路径组件]
| 操作符 | 描述 | 示例 |
| ----------- | --------- | --------------------- |
| $ | 根对象 | $ |
| . 或 [] | 成员访问 | $.name 或 $['name'] |
| [*] | 数组通配符 | $.items[*] |
| [n] | 数组索引 | $[0] |
| [m to n] | 数组范围 | $[1 to 3] |
| ** | 递归通配符 | $**.price |
$
)$
表示整个 JSON 文档.
或 []
)$.store.book
$['store']['book']
$[*]
或 $.array[*]
$[0]
计数是从0开始$[1 to 3]
(MySQL 8.0.26+)*
匹配当前层级所有成员/元素**
递归搜索所有路径(MySQL 8.0.26+)$.items[?(@.price > 10)]
?
引入过滤表达式@
表示当前元素$[1 to 3] // 第1到第3个元素
$[last-1] // 倒数第二个元素
$[last-2 to last] // 最后三个元素
-- 提取标量值
SELECT JSON_EXTRACT('{"name": "张三", "age": 30}', '$.name');
-- 数组元素, 输出 "b", 注意是带双引号的
SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');
-- 嵌套对象
SELECT JSON_EXTRACT('{"store": {"book": {"title": "MySQL指南"}}}', '$.store.book.title');
-- 对象数组
SELECT JSON_EXTRACT('{"items": [{"id": 1}, {"id": 2}]}', '$.items[*].id');
-- 查找高价商品
SELECT JSON_EXTRACT('{"items": [{"id": 1, "price": 5}, {"id": 2, "price": 15}]}',
'$..items[?(@.price > 10)].id');
MySQL 提供常用操作的简写形式
->
: 等同于 JSON_EXTRACT()
->>
: 等同于 JSON_UNQUOTE(JSON_EXTRACT())
-- 以下两种写法等价:
SELECT json_column->'$.name';
SELECT JSON_EXTRACT(json_column, '$.name');
-- 以下两种写法等价(返回去除引号的字符串):
SELECT json_column->>'$.name';
SELECT JSON_UNQUOTE(JSON_EXTRACT(json_column, '$.name'));
**
递归操作符可能影响性能=
、!=
、、>
等
使用过 JSON_EXTRACT 函数都知道, 这样获取的结果还不是真正的行列结构, MySQL 8.0 引入的 JSON_TABLE 函数可以将 JSON 数据转换为关系型表格格式, 将数组中的每个元素转换成表格中的一行数据.
JSON_TABLE 的功能
JSON_TABLE(
json_doc, -- JSON 类型的字段或值
path_expression -- JSON 路径表达式
COLUMNS( -- 新表的列定义
column_name column_type PATH json_path [on_empty] [on_error],
...
)
) [AS] alias
参数说明
column_name
:生成的列名column_type
:数据类型(如 VARCHAR, INT, JSON 等)PATH
:指定数据提取路径SELECT *
FROM JSON_TABLE(
'[1, 2, 3]',
'$[*]' COLUMNS(
rowid FOR ORDINALITY,
value INT PATH '$'
)
) AS t;
输出
rowid | value
------+-------
1 | 1
2 | 2
3 | 3
SELECT *
FROM JSON_TABLE(
'[{"name":"张三","age":25},{"name":"李四","age":30}]',
'$[*]' COLUMNS(
name VARCHAR(20) PATH '$.name',
age INT PATH '$.age',
adult VARCHAR(3) PATH '$.age' DEFAULT '否' ON EMPTY
)
) AS t;
输出
name | age | adult
-----+-----+------
张三 | 25 | 否
李四 | 30 | 否
如果JSON是表中的一个字段, 可以使用 table_1 CROSS JOIN JSON_TABLE(...)
展开, 例如一个表 v_video 的字段 result 为 JSON 字段, 需要展开 result 中的一个成员 sequences, 写成SQL如下
SELECT
e.id,
e.match_id,
e.result->>'$.id' AS json_id,
j.tag->>'$.sf' AS sf_value,
j.tag->>'$.ef' AS ef_value,
j.tag->>'$.ef' - j.tag->>'$.sf'AS duration
FROM
v_video e
CROSS JOIN JSON_TABLE(
e.result->'$.sequences',
'$[*]' COLUMNS (
tag JSON PATH '$'
)
) AS j ON e.match_id = 294
上面的SQL, 通过 CROSS JOIN JSON_TABLE 将每一行 e.result 字段下的 sequences 数组展开, 每个数组元素成为新字段 tag, 这时候还是一个 JSON, 然后在SELECT 中通过->>
抽取其中的值, 得到完全展开的一个新表.
生成自增的行号列
COLUMNS(
id FOR ORDINALITY,
...
)
COLUMNS(
NESTED PATH '$.nested_obj' COLUMNS(
sub_col1 INT PATH '$.prop1',
sub_col2 VARCHAR(10) PATH '$.prop2'
)
)
上面的例子用嵌套可以改写为
SELECT
j.id,
j.sf,
j.ef,
j.ef - j.sf AS duration
FROM
v_video e
CROSS JOIN
JSON_TABLE(
e.result->'$.sequences',
'$[*]' COLUMNS (
id FOR ORDINALITY,
NESTED PATH '$' COLUMNS(
ef INT PATH '$.ef',
sf INT PATH '$.sf'
)
)
) AS j ON e.match_id = 294
上面的SQL, 通过 NESTED PATH ... COLUMNS(...)
将展开后数组中的一个JSON元素进一步展开为多个字段.
COLUMNS(
ef INT PATH '$.ef' NULL ON EMPTY NULL ON ERROR,
sf INT PATH '$.sf' DEFAULT '0' ON EMPTY NULL ON ERROR
)
格式是
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR
参与评论
手机查看
返回顶部