将oracle表字段json字符串分解提取并返回单列表
oracle 版本
11gR2(11.2.0.4.0)
原始表字段内容
{
"FRAME_INFO": [
"0,0,-1,1800,1800,5992,191,20",
"1,0,-1,2000,1800,5992,188,20",
"2,0,-1,1800,1800,5992,182,20",
"3,0,-1,1800,1800,5992,177,20",
"4,0,-1,3700,1800,6005,171,20",
"5,0,-1,19200,1800,5992,167,20",
"6,0,-1,12000,1800,6005,163,20",
"7,0,-1,6200,1800,6005,159,20",
"8,0,-1,4700,1800,6005,154,20",
"9,0,-1,9900,1800,6005,150,20",
"10,0,-1,6500,1800,5992,147,20"
],
"PLAY_IDX": [
"0,4,6,4",
"1,4,6,4",
"2,5,6,5",
"3,5,6,5",
"4,5,7,5",
"5,5,7,5",
"6,6,7,5",
"7,6,8,5",
"8,6,8,5",
"9,6,8,6",
"10,7,8,6",
"5,5,7,5"
],
"IRV_NUM": 11,
"VI_NUM": 13,
"OVA_NUM": 15,
"OVB_NUM": 0,
"IRV_DIR": "20250708000540_1_CRH2A-2229_720_0_B",
"VI_DIR": "20250708000540_2_CRH2A-2229_720_0_B_M",
"OVA_DIR": "20250708000540_3_CRH2A-2229_720_0_B",
"OVB_DIR": "20250708000540_4_CRH2A-2229_720_0_B",
"START_IDX": 1,
"FAULT_IDX": 5,
"VI_IDX": 5,
"OVA_IDX": 7,
"OVB_IDX": 5,
"FLAG": [
0,
0,
0,
0
]
}
需求是将FRAME_INFO部分每行提取出来
提取FRAME_INFO部分
regexp_substr(a.svalue3, '"FRAME_INFO":[^]]+'
提取每一行
TABLE( --
CAST( --
MULTISET --
(
SELECT regexp_substr(regexp_substr(a.svalue3, '"FRAME_INFO":[^]]+'), '\d[^"]+', 1, LEVEL)
FROM dual
CONNECT BY regexp_substr(regexp_substr(a.svalue3, '"FRAME_INFO":[^]]+'), '\d[^"]+', 1, LEVEL) IS NOT NULL --
) --
AS SYS.ODCIVARCHAR2LIST
)
) t
实现完整SQL
SELECT ID, t.* --regexp_substr(regexp_substr(a.svalue3, '"FRAME_INFO":[^]]+'), '\d[^"]+', 1, 3)
FROM alarm a,
TABLE( --
CAST( --
MULTISET --
(
SELECT regexp_substr(regexp_substr(a.svalue3, '"FRAME_INFO":[^]]+'), '\d[^"]+', 1, LEVEL)
FROM dual
CONNECT BY regexp_substr(regexp_substr(a.svalue3, '"FRAME_INFO":[^]]+'), '\d[^"]+', 1, LEVEL) IS NOT NULL --
) --
AS SYS.ODCIVARCHAR2LIST
)
) t
WHERE a.raised_time > TRUNC(SYSDATE)
AND a.status = 'AFSTATUS03'
AND a.id = 'F060ee2f14b7942d88b01832551a457d2';
验证查询结果
| id | column_values |
|---|---|
| F060ee2f14b7942d88b01832551a457d2 | 0,0,-1,1800,1800,5992,191,20 |
| F060ee2f14b7942d88b01832551a457d2 | 1,0,-1,2000,1800,5992,188,20 |
| F060ee2f14b7942d88b01832551a457d2 | 2,0,-1,1800,1800,5992,182,20 |
| F060ee2f14b7942d88b01832551a457d2 | 3,0,-1,1800,1800,5992,177,20 |
| F060ee2f14b7942d88b01832551a457d2 | 4,0,-1,3700,1800,6005,171,20 |
| F060ee2f14b7942d88b01832551a457d2 | 5,0,-1,19200,1800,5992,167,20 |
| F060ee2f14b7942d88b01832551a457d2 | 6,0,-1,12000,1800,6005,163,20 |
| F060ee2f14b7942d88b01832551a457d2 | 7,0,-1,6200,1800,6005,159,20 |
| F060ee2f14b7942d88b01832551a457d2 | 8,0,-1,4700,1800,6005,154,20 |
| F060ee2f14b7942d88b01832551a457d2 | 9,0,-1,9900,1800,6005,150,20 |
| F060ee2f14b7942d88b01832551a457d2 | 10,0,-1,6500,1800,5992,147,20 |