--从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