Oracle JSON 字符串处理实例

来源：互联网发布：淘宝云客服在哪里编辑：程序博客网时间：2024/04/30 04:54
--从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
0 0