json_table使用
- 方式一
SELECT
FR.*
FROM dual ,
JSON_TABLE(‘[{"name":"hziwei", "age": 13},{"name":"hziwei", "age": 15}]‘,‘$[*]‘
COLUMNS ( name VARCHAR2(10) PATH ‘$.name‘,
age VARCHAR2(10) PATH ‘$.age‘
)) AS FR where age > 13;
- 方式二
SELECT
FR.*
FROM dual ,
JSON_TABLE(‘{"data": [{"name":"hziwei", "age": 13},{"name":"hziwei", "age": 15}]}‘,‘$.data[*]‘
COLUMNS (name VARCHAR2(10) PATH ‘$.name‘,
age NUMBER PATH ‘$.age‘
)) AS FR
;
- 从表中的json数组字段转换为表
表结构 表名:TEST
SELECT
FR.*
FROM TEST,
JSON_TABLE(CONTENT,‘$[*]‘
COLUMNS ( name VARCHAR2(10) PATH ‘$.name‘,
age NUMBER PATH ‘$.age‘
)) AS FR
;