JSON_TABLE()¶
简介¶
从JSON文档中提取数据，并将其作为具有指定列的关系表返回。
语法¶
JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)
(1)column_list：column[, column][, ...]
(2)column：name FOR ORDINALITY
｜name type PATH string path [on_empty] [on_error]
｜name type EXISTS PATH string path
(3)on_empty：
{NULL | DEFAULT json_string | ERROR} ON EMPTY
(4)on_error：
{NULL | DEFAULT json_string | ERROR} ON ERROR
说明¶
1. expr：传入的json数据
2. path：指定json路径，后续构造的数据都要依赖于此路径。
3. name FOR ORDINALITY：名称为name的一个自增列，起始值为1。
4. name type PATH string path [on_empty] [on_error]：从指定的path路径获取数据，转换其类型为type。若获取数据为空，则触发[on empty]子句，若指定路径数据与type不能相互转换，则触发[on error]子句。
5. name type EXISTS PATH string path：若path存在值，则返回1，否则返回0，type通常情况下设为int。
6. on_empty：如果构造列时给定路径PATH上没有发现数据时，该子句取以下值之一:
(1)NULL ON EMPTY：向表中插入NULL，如果没有使用ON EMPTY子句，默认插入NULL。
(2)DEFAULT value ON EMPTY：向表中插入默认值，默认值的类型必须与此列所定义的类型匹配。
(3)ERROR ON EMPTY：抛出一个错误。
7. on_error：当构造列时发生错误，该子句取以下值之一:
(1)NULL ON ERROR：向表中插入NULL，如果没有使用ON ERROR子句，默认返回NULL。
(2)DEFAULT value ON ERROR：向表中插入默认值，默认值的类型必须与此列所定义的类型匹配。
(3)ERROR ON ERROR：抛出一个错误。
8. alias：别名，必须要有一个别名，没有会报错。
示例¶
-- 构造两列数据number和name
SELECT * FROM JSON_TABLE('[{"number":"1","name":"kobe"},{"number":"2","name":"jordan"},{"number":"3","name":"harden"}]','$[*]' columns(number int path '$.number', name varchar(10) path '$.name')) AS T1;
NUMBER(int) |NAME(varchar) |
------------------------------------
1 |kobe |
------------------------------------
2 |jordan |
------------------------------------
3 |harden |
总数目：3
-- 构造一个自增列
SELECT * FROM JSON_TABLE('[{"number":"1","name":"kobe"},{"number":"2","name":"jordan"},{"number":"3","name":"harden"}]','$[*]' columns(rowid FOR ORDINALITY,number int path '$.number', name varchar(10) path '$.name')) AS T1;
ROWID(int) |NUMBER(int) |NAME(varchar) |
----------------------------------------------------
1 |1 |kobe |
----------------------------------------------------
2 |2 |jordan |
----------------------------------------------------
3 |3 |harden |
总数目：3
-- 指定路径下找不到json值，触发on empty
SELECT * FROM JSON_TABLE('[{"name":"kobe"},{"name":"jordan"},{"name":"harden"}]','$[*]' columns(number int path '$.number' NULL on empty, name varchar(10) path '$.name')) AS T1;
NUMBER(int) |NAME(varchar) |
------------------------------------
null |kobe |
------------------------------------
null |jordan |
------------------------------------
null |harden |
总数目：3
SELECT * FROM JSON_TABLE('[{"name":"kobe"},{"name":"jordan"},{"name":"harden"}]','$[*]' columns(number int path '$.number' default '-1' on empty, name varchar(10) path '$.name')) AS T1;
NUMBER(int) |NAME(varchar) |
------------------------------------
-1 |kobe |
------------------------------------
-1 |jordan |
------------------------------------
-1 |harden |
总数目：3
SELECT * FROM JSON_TABLE('[{"name":"kobe"},{"name":"jordan"},{"name":"harden"}]','$[*]' columns(number int path '$.number' error on empty, name varchar(10) path '$.name')) AS T1;
ERROR, Missing value for JSON_TABLE column NUMBER.
-- 指定路径下的json值，与列类型不同，触发on error
SELECT * FROM JSON_TABLE('[{"number":"abc","name":"kobe"},{"number":"2","name":"jordan"},{"number":"def","name":"harden"}]','$[*]' columns(number int path '$.number' NULL on error, name varchar(10) path '$.name')) AS T1;
NUMBER(int) |NAME(varchar) |
------------------------------------
null |kobe |
------------------------------------
2 |jordan |
------------------------------------
null |harden |
总数目：3
SELECT * FROM JSON_TABLE('[{"number":"abc","name":"kobe"},{"number":"2","name":"jordan"},{"number":"def","name":"harden"}]','$[*]' columns(number int path '$.number' default '-1' on error, name varchar(10) path '$.name')) AS T1;
NUMBER(int) |NAME(varchar) |
------------------------------------
-1 |kobe |
------------------------------------
2 |jordan |
------------------------------------
-1 |harden |
总数目：3
SELECT * FROM JSON_TABLE('[{"number":"abc","name":"kobe"},{"number":"2","name":"jordan"},{"number":"def","name":"harden"}]','$[*]' columns(number int path '$.number' error on error, name varchar(10) path '$.name')) AS T1;
ERROR, 错误的数值格式 'abc'
-- EXISTS PATH用例
SELECT * FROM JSON_TABLE('[{"number":"1","name":"kobe"},{"number":"2"},{"number":"3"}]','$[*]' columns(number int path '$.number', name varchar(10) path '$.name',namePath int exists path '$.name')) AS T1;
NUMBER(int) |NAME(varchar) |NAMEPATH(int) |
-------------------------------------------------------
1 |kobe |1 |
-------------------------------------------------------
2 |null |0 |
-------------------------------------------------------
3 |null |0 |
总数目：3