mysql的jsonTable使用及说明
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">以下是jsontable的语法</a></li><li><a href="#_label1">使用示例</a></li><li><a href="#_label2">NESTED PATH 嵌套路径</a></li><li><a href="#_label3">横向派生表</a></li><li><a href="#_label4">总结</a></li></ul></div><p><strong>JSON_TABLE</strong> 是 MySQL 8.0 中一个新的 JSON 函数。它也是一个表函数，返回值不是标量值而是结果集。</p><p><strong>JSON_TABLE</strong> 将 JSON 文档(部分或全部)转换为关系表，以便您可以像使用普通表一样使用它。</p>
<p><strong>JSON_TABLE</strong> 函数从一个指定的 JSON 文档中提取数据并返回一个具有指定列的关系表。可以像普通的表一样使用 <strong>JSON_TABLE ()</strong> 返回的表。</p>
<p>json-table官方文档地址：</p>
<p><a href="https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html" rel="external nofollow" target="_blank">https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html</a></p>
<p class="maodian"><a name="_label0"></a></p><h2>以下是jsontable的语法</h2>
<div class="jb51code"><pre class="brush:sql;">JSON_TABLE(
expr,
path COLUMNS (column_list)
) alias
column_list:
column[, column][, ...]
column:
name FOR ORDINALITY
|name type PATH string path
|name type EXISTS PATH string path
|NESTED path COLUMNS (column_list)
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>使用示例</h2>
<p>假设我们有一个表包含ID和名称：</p>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE t1
(
id varchar(32),
name JSON
);
</pre></div>
<p>添加如下数据：</p>
<div class="jb51code"><pre class="brush:sql;">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'));
</pre></div>
<p>现在，我们想要从 t1 表中查询所有名称和id，进行查询（在 MySQL 中，JSON_TABLE 函数的 JSON 路径表达式可以使用 $ 符号来指定。</p>
<p><strong>$[*]</strong> 表示提取 JSON 文档中的所有属性）：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT value,id
from t1
,
JSON_TABLE(name, '$[*]' COLUMNS (
value VARCHAR(255) PATH '$'
)) AS jt
</pre></div>
<p>数据将如下：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010411250480.png" /></p>
<p class="maodian"><a name="_label2"></a></p><h2>NESTED PATH 嵌套路径</h2>
<p>NESTED PATH为其所属的子句中的每个匹配项生成一组记录。可以解决复杂的json嵌套数据转为行。</p>
<p>添加一条数据：</p>
<div class="jb51code"><pre class="brush:sql;">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)));
</pre></div>
<p>使用 NESTED PATH：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT jt.*
FROM t1,
JSON_TABLE(
name,
'$[*]' COLUMNS (
a INT PATH '$.a',
NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$')
)
) AS jt
WHERE id = '1';
</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010411250486.png" /></p>
<p>我们发现每次jsontable后依然会对应所在的ID，比如：</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202601/2026010411250479.png" /></p>
<p>上面需求的a,b列的ID就是1而不是其他ID。这其实是遵从了sql标准。</p>
<p class="maodian"><a name="_label3"></a></p><h2>横向派生表</h2>
<p>根据SQL标准，MySQL总是将带有表函数（如JSON_TABLE（））的连接视为使用LATERAL无论MySQL发布版本如何，都是如此，这就是为什么即使在8.0.14之前的MySQL版本中也可以加入此函数的原因。</p>
<p>在MySQL 8.0.14及更高版本中，LATERAL 关键字是隐式的，不允许在 JSON_TABLE（） 之前使用。这也是根据SQL标准。</p>
<p class="maodian"><a name="_label4"></a></p><h2>总结</h2>
<p>以上为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>
頁:
[1]