Oracle JSON 字符串处理实例

--从JSONArray中取数据
SELECT jt.*
FROM JSON_TABLE('[
        {
            "device_type_id": 1,
            "amount": 120,
            "remarks": ""
        },
        {
            "device_type_id": 2,
            "amount": 122,
            "remarks": ""
        },
        {
            "device_type_id": 3,
            "amount": 123,
            "remarks": "11111111111"
        }
    ]','$'COLUMNS(NESTED PATH '$[*]' COLUMNS (device_type_id VARCHAR2(32) PATH '$.device_type_id',amount VARCHAR2(32) PATH '$.amount',remarks VARCHAR2(32) PATH '$.remarks')))
AS jt;

--从JSONObject对象中取数据
SELECT jt.*
FROM JSON_TABLE('{
    "detailed": [
        {
            "device_type_id": 1,
            "amount": 120,
            "remarks": ""
        },
        {
            "device_type_id": 2,
            "amount": 122,
            "remarks": ""
        }
    ]
}
','$'
COLUMNS
(NESTED PATH '$.detailed[*]' COLUMNS (device_type_id VARCHAR2(32) PATH '$.device_type_id',amount VARCHAR2(32) PATH '$.amount',remarks VARCHAR2(32) PATH '$.remarks')))
AS jt;

SELECT *
  FROM JSON_TABLE('{
                    "device_type_id": "1",
                    "amount": "120",
                    "remarks": ""
                }
',
                   '$' COLUMNS(outer_value_0 NUMBER PATH '$.device_type_id',
                           outer_value_1 NUMBER PATH '$.amount'));

--从三层嵌套的JSONObject对象中取数据
SELECT jt.*
FROM JSON_TABLE('{
    "certificate": "14531209693428a799591c0248bb95c3",
    "rows": [
        {
            "odo_id": "0",
            "odo_no": "ZC-FY-20170217001",
            "stamp": "2017-02-24",
            "order_no": "ZC-DD-20170210001",
            "partners_id": "213",
            "shipping_address": "深圳市福田区科技园南区T2-B栋601",
            "contacts": "李魁",
            "tel": "13510141822",
            "self_mention": "0",
            "detailed": [
                {
                    "device_type_id": "1",
                    "amount": "121",
                    "remarks": ""
                },{
                    "device_type_id": "2",
                    "amount": "122",
                    "remarks": ""
                }
            ]
        },{
            "odo_id": "0",
            "odo_no": "ZC-FY-20170217002",
            "stamp": "2017-02-24",
            "order_no": "ZC-DD-20170210001",
            "partners_id": "213",
            "shipping_address": "深圳市福田区科技园南区T2-B栋601",
            "contacts": "李魁",
            "tel": "13510141822",
            "self_mention": "0",
            "detailed": [
                {
                    "device_type_id": "3",
                    "amount": "123",
                    "remarks": ""
                },{
                    "device_type_id": "4",
                    "amount": "124",
                    "remarks": ""
                }
            ]
        }
    ]
}
', '$'
COLUMNS
  (requestor VARCHAR2(32) PATH '$.certificate',NESTED PATH '$.rows[*]' COLUMNS (odo_no VARCHAR2(32) PATH '$.odo_no',NESTED PATH '$.detailed[*]' COLUMNS (phone_type VARCHAR2(32) PATH '$.device_type_id', phone_num VARCHAR2(20) PATH '$.amount'))))
AS jt;

官方示例连接：

http://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973