如何在Oracle中获取JSON字符串的一部分

Question

我有类似这样的数据来自一个领域-

{"AddressLine1":"Example Dr "，" AddressLine2 ":"，" City":"Paradise City "，" State":"State Name "，" PostalCode":"12345 "，" Zip5":"67891 "，" zip 4 ":" }

{"地址线1"："Ex Ln"，"地址线2"："Adr 22"，"城市"："，"州"："州名"，"邮政编码"："12345"，"Zip5"："67891"，"Zip4"："}

我需要在“城市”旁边的引号中提取值：。在上述情况下，我需要值 - 天堂城和 NULL。

Answer 1

您可以使用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 | CITY          | STATE      | POSTALCODE | ZIP5  | ZIP4
-: | :----------- | :----------- | :------------ | :--------- | :--------- | :---- | :---
 1 | Example Dr   | null         | Paradise City | State Name | 12345      | 67891 | null
 2 | Ex Ln        | Adr 22       | null          | State Name | 12345      | 67891 | null
 3 | null         | null         | null          | null       | null       | null  | null

db