我有这样的数据来自字段->
{AddressLine1“:” Example Dr“,” AddressLine2“:”“,” City“:”天堂城市“,” State“:”州名称“,” PostalCode“:” 12345“,” Zip5“:” 67891“, “ Zip4”:“”}
{AddressLine1“:” Ex Ln“,” AddressLine2“:” Adr 22“,” City“:”“,” State“:”州名称“,” PostalCode“:” 12345“,” Zip5“:” 67891“, “ Zip4”:“”}
我需要提取“城市”旁边引号中的值:在上述情况下,我需要值-Paradise City和NULL。
您可以使用JSON_TABLE
:
SELECT t.id,
jt.*
FROM table_name t
OUTER APPLY JSON_TABLE(
t.value,
'$'
ERROR ON ERROR
COLUMNS (
AddressLine1 VARCHAR2(200) PATH '$.AddressLine1',
AddressLine2 VARCHAR2(200) PATH '$.AddressLine2',
City VARCHAR2(200) PATH '$.City',
State VARCHAR2(200) PATH '$.State',
PostalCode VARCHAR2(7) PATH '$.PostalCode',
Zip5 VARCHAR2(5) PATH '$.Zip5',
Zip4 VARCHAR2(4) PATH '$.Zip4'
)
) jt;
其中,对于示例数据:
CREATE TABLE table_name ( id INT, value CLOB CHECK ( value IS JSON ) );
INSERT INTO table_name ( id, value )
SELECT 1, '{"AddressLine1":"Example Dr","AddressLine2":"","City":"Paradise City","State":"State Name","PostalCode":"12345","Zip5":"67891","Zip4":""}' FROM DUAL UNION ALL
SELECT 2, '{"AddressLine1":"Ex Ln","AddressLine2":"Adr 22","City":"","State":"State Name","PostalCode":"12345","Zip5":"67891","Zip4":""}' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL;
输出:
ID | ADDRESSLINE1 | ADDRESSLINE2 | 城市| 州| 邮政编码| ZIP5 | ZIP4- :| :----------- | :----------- | :------------ | :--------- | :--------- | :---- | : -1 示例博士| 空 | 天堂之城| 州名| 12345 | 67891 | null 2 | Ex Ln | Adr 22 | 空 | 州名| 12345 | 67891 | null 3 | 空 | 空 | 空 | 空 | 空 | 空 | 空值
db <>在这里拨弄
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句