mysql将字符串转换数字类型_使用Oracle PL / SQL将字符串转换为JSON对象
你可以：
创建一个外部表来读取文件
使用JSON_table将文档转换为关系行和列
看起来有点像：
/* Create the file */
create or replace directory tmp as '/tmp';
declare
f utl_file.file_type;
begin
f := utl_file.fopen ('TMP', 'input.json', 'w');
utl_file.put_line ( f, '{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }');
utl_file.fclose(f);
end;
/
create table json_ext (
json_doc varchar2(100)
) organization external (
default directory tmp
access parameters (
records delimited by newline
fields (
json_doc char(1000)
)
)
location ( 'input.json' )
);
select * from json_ext;
JSON_DOC
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] }
select *
from json_ext,
json_table (
json_doc, '$'
columns (
nested path '$.CAR[*]' columns (
CAR path '$'
),
nested path '$.NAME[*]' columns (
NAME path '$'
)
)
);
JSON_DOC CAR NAME
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] } %HON%
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] } %UZU%
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] } %RAY%
{ "CAR":["%HON%","%UZU%"], "NAME":["%RAY%","%OE%"] } %OE%
This splits each array into its own set of rows and columns. To get this as a single list of attribute names and array values, you can unpivot the results:
with rws as (
select j.*
from json_ext,
json_table (
json_doc, '$'
columns (
nested path '$.CAR[*]' columns (
CAR path '$'
),
nested path '$.NAME[*]' columns (
NAME path '$'
)
)
) j
)
select * from rws
unpivot (
val for attr in ( CAR, NAME )
);
ATTR VAL
CAR %HON%
CAR %UZU%
NAME %RAY%
NAME %OE%