如何在oracle中解析clob数据

Question

我需要在oracle中解析clob数据。我想找到最佳实践。它可能是过程、函数或脚本。我能为解决这个问题做些什么？

我的桌子，

克隆数据，

分析结果表应该是这样的

Answer 1

如果您有Oracle版本>=，我的第一个选项将是JSON_TABLE12.1，但这是一个替代选项I ab(使用(定时

with a as (
select 'ID01' AS ID,'"A1":{"B1":"C1"},"A2":{"B2":"C2"},"A3":{"B3":"C3"}' AS B FROM DUAL
UNION
select 'ID02' AS ID,'"B1":{"C1":"D1"},"B2":{"C2":"D2"},"B3":{"C3":"D3"}' AS B FROM DUAL
union
select 'ID03' AS ID,'"tablex":{"Cola":"colb"},"B2":{"C2":"D2"}' AS B FROM DUAL 
)
SELECT CLOBID,
REGEXP_SUBSTR(CLOB_D,'"(.+?)"',1,1,null,1) AS Col1,
REGEXP_SUBSTR(CLOB_D,'"(.+?)"',1,2,null,1) AS Col2,
REGEXP_SUBSTR(CLOB_D,'"(.+?)"',1,3,null,1) AS Col3
FROM(
select ID AS CLOBID,LEVEL,REGEXP_COUNT(B,',',1), regexp_substr(B,'[^,]+',1,LEVEL,NULL) CLOB_D
FROM A
CONNECT BY (LEVEL - 1) <= REGEXP_COUNT(B,',',1)
AND ID = PRIOR ID
AND PRIOR DBMS_RANDOM.VALUE() IS NOT NULL
);