我要评论json_table 是 mysql 8.0 中一个新的 json 函数。它也是一个表函数，返回值不是标量值而是结果集。
json_table 将 json 文档(部分或全部)转换为关系表，以便您可以像使用普通表一样使用它。
json_table 函数从一个指定的 json 文档中提取数据并返回一个具有指定列的关系表。可以像普通的表一样使用 json_table () 返回的表。
json-table官方文档地址：
https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table
以下是jsontable的语法
json_table(
expr,
path columns (column_list)
) [as] alias
column_list:
column[, column][, ...]
column:
name for ordinality
| name type path string path [on_empty] [on_error]
| name type exists path string path
| nested [path] path columns (column_list)
on_empty:
{null | default json_string | error} on empty
on_error:
{null | default json_string | error} on error
使用示例
假设我们有一个表包含id和名称：
create table t1
(
id varchar(32),
name json
);
添加如下数据：
insert into t1
values (uuid_short(), json_array('john'));
insert into t1
values (uuid_short(), json_array('toma','smith'));
insert into t1
values (uuid_short(), json_array('aa','bb','cc','tt'));
现在，我们想要从 t1 表中查询所有名称和id，进行查询（在 mysql 中，json_table 函数的 json 路径表达式可以使用 $ 符号来指定。
$[*] 表示提取 json 文档中的所有属性）：
select value,id
from t1
,
json_table(name, '$[*]' columns (
value varchar(255) path '$'
)) as jt
数据将如下：
nested path 嵌套路径
nested path为其所属的子句中的每个匹配项生成一组记录。可以解决复杂的json嵌套数据转为行。
添加一条数据：
insert into t1
values ('1', json_array(json_object('a',1,'b',json_array(11,111)),
json_object('a',2, 'b',json_array(22,222)),
json_object('a',3)));
使用 nested path：
select jt.*
from t1,
json_table(
name,
'$[*]' columns (
a int path '$.a',
nested path '$.b[*]' columns (b int path '$')
)
) as jt
where id = '1';
我们发现每次jsontable后依然会对应所在的id，比如：
上面需求的a,b列的id就是1而不是其他id。这其实是遵从了sql标准。
横向派生表
根据sql标准，mysql总是将带有表函数（如json_table（））的连接视为使用lateral无论mysql发布版本如何，都是如此，这就是为什么即使在8.0.14之前的mysql版本中也可以加入此函数的原因。
在mysql 8.0.14及更高版本中，lateral 关键字是隐式的，不允许在 json_table（） 之前使用。这也是根据sql标准。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论