有一个请求例如:
with j (sJson) as (
select '{
"ID":"1444284517",
"ID_ORD":"4255;2187606199",
"Vals":{
"CODE":"ONB2B3BB8",
"DORD":"25.04.2021"
}
}'
from dual
)
select jt.*
from j
cross apply json_table (j.sJson, '$'
columns
ID varchar2(32) path '$.ID',
ID_ORD varchar2(32) path '$.ID_ORD',
nested path '$.Vals[*]'
columns (
CODE varchar2(9) path '$.CODE',
DORD varchar2(30) path '$.DORD',
....
)) jt
In column
可能有不同的领域。
如何列出所有字段columns
不指定类型和路径?那如何让它成为动态解析器呢?需要放弃CODE varchar2(9) path '$.CODE'
我期望这个结果:
| ID | ID_ORD | CODE | DORD |
+------------+-----------------+-----------+------------+
| 1444284517 | 4255;2187606199 | ONB2B3BB8 | 25.04.2021 |
您可以定义函数:
CREATE FUNCTION get_keys(
value IN CLOB
) RETURN SYS.ODCIVARCHAR2LIST PIPELINED
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
keys JSON_KEY_LIST;
BEGIN
keys := js.get_keys();
FOR i in 1 .. keys.COUNT LOOP
PIPE ROW ( keys(i) );
END LOOP;
END;
/
CREATE FUNCTION get_value(
value IN CLOB,
path IN VARCHAR2
) RETURN VARCHAR2
IS
js JSON_OBJECT_T := JSON_OBJECT_T( value );
BEGIN
RETURN js.get_string( path );
END;
/
然后使用查询:
WITH j (sJson) as (
select '{
"ID":"1444284517",
"ID_ORD":"4255;2187606199",
"Vals":{
"CODE":"ONB2B3BB8",
"DORD":"25.04.2021"
}
}'
from dual
)
SELECT jt.id,
jt.id_ord,
k.COLUMN_VALUE AS Key,
get_value( jt.vals, k.COLUMN_VALUE ) AS value
FROM j
CROSS APPLY JSON_TABLE(
j.sjson,
'$'
COLUMNS (
id VARCHAR2(20) PATH '$.ID',
id_ord VARCHAR2(30) PATH '$.ID_ORD',
vals VARCHAR2(4000) FORMAT JSON PATH '$.Vals'
)
) jt
CROSS APPLY get_keys( jt.vals ) k
哪个输出:
ID |
ID_ORD |
KEY |
VALUE |
1444284517 |
4255;2187606199 |
CODE |
ONB2B3BB8 |
1444284517 |
4255;2187606199 |
DORD |
25.04.2021 |
(注意:SQL不支持动态列数,因此您需要提供固定列数,例如key
and value
并将输出作为行而不是列。)
数据库小提琴
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)