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%