Oracle解析JSON变量到表

Question

我需要编写一个过程，该过程将接受CLOB类型的参数，该参数实际上是一个JSON文本字符串，解析该字符串，并将其插入到表中。JSON中的字段与表中的列的顺序相同。

字符串看起来像这样:

{
"signal_id": "1",
"ts_id": "3",
"add_price": "0",
"qty": "1",
"stops": "0.00",
"yield": "0.00",
"close_date": "NULL",
"close_price": "0.00",
"ticker": "IBM",
"option_ticker": "NULL",
"signal_date": "2012-07-25",
"estimated_reporting_date": "NULL",
"signal_val": "1",
"comp_name": "INTERNATIONA",
"lt_price": "190.34",
"sell_target": "NULL",
"high_target": "NULL",
}

解析并插入到表中的最佳方法是什么?

Answer 1

使用JSON_TABLE:

CREATE PROCEDURE insert_json (i_json IN CLOB)
IS
BEGIN
INSERT INTO your_table (
signal_id, ts_id, add_price, qty, stops, yield, close_date, close_price,
ticker, option_ticker, signal_date, estimated_reporting_date
/*...*/
)
SELECT *
FROM   JSON_TABLE(
i_json,
'$'
COLUMNS(
signal_id     NUMBER          PATH '$.signal_id',
ts_id         NUMBER          PATH '$.ts_id',
add_price     NUMBER          PATH '$.add_price',
qty           NUMBER          PATH '$.qty',
stops         NUMBER          PATH '$.stops',
yield         NUMBER          PATH '$.yield',
close_date    DATE            PATH '$.close_date',
close_price   NUMBER          PATH '$.close_price',
ticker        VARCHAR2(10)    PATH '$.ticker',
option_ticker VARCHAR2(10)    PATH '$.option_ticker',
signal_date   DATE            PATH '$.signal_date',
estimated_reporting_date DATE PATH '$.estimated_reporting_date'
-- ...
)
);
END insert_json;
/

db<此处小提琴>