我有以下数组:
"Sorted Reason Code Table": [
"PCC018",
"PCC020",
"PCC021",
"PCC023",
"PCC025",
"PCC031",
"PCC059",
"PCC061",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
]
我想得到字符串中的所有值。输出应为:
PCC010,PCC012,PCC0034 etc.
所有值都是动态的,有时会有值,有时会为空。
如有任何帮助,我们将不胜感激。
您可以使用:
SELECT LISTAGG(reason_code, ',') WITHIN GROUP (ORDER BY ROWNUM) AS reason_codes
FROM table_name t
CROSS APPLY JSON_TABLE(
t.value,
'$."Sorted Reason Code Table"[*]'
COLUMNS (
reason_code VARCHAR2(10) PATH '$'
)
) j
WHERE reason_code IS NOT NULL
GROUP BY t.ROWID
对于样本数据:
CREATE TABLE table_name (value BLOB CHECK (value IS JSON));
INSERT INTO table_name (value)
VALUES ('{"Sorted Reason Code Table": ["PCC018","PCC020","PCC021","PCC023","PCC025","PCC031","PCC059","PCC061","","","","","","","","","","",""]}');
输出:
REASON_CODES PCC018、PCC020、PCC021、PCC023、PCC025、PCC031、PCC059、PCC061 您可以这样做(滥用
JSON_ARRAYAGG
函数,告诉它原因代码的格式是JSON,而事实上它们不是JSON(。使用MT0答案中的数据。select substr(lst, 2, length(lst) - 2) as reason_codes from ( select json_arrayagg(reason_code format json order by reason_code) as lst from table_name t cross apply json_table(t.value, '$."Sorted Reason Code Table"[*]' columns (reason_code varchar2(10) path '$') ) ) ; REASON_CODES ---------------------------------------------------------- PCC018,PCC020,PCC021,PCC023,PCC025,PCC031,PCC059,PCC061