JSON_ELEMENT_T¶

JSON_ELEMENT_T是JSON_OBJECT_T、JSON_SCALAR_T、JSON_ARRAY_T的父类型。JSON_ELEMENT_T向子类型的转换和子类型向JSON_ELEMENT_T的转换可以通过TREAT AS实现。

JSON_ELEMENT_T对象类型包含的子程序如下：

构造函数¶

STATIC FUNCTION parse(json VARCHAR2) RETURN JSON_ELEMENT_T
STATIC FUNCTION parse(json CLOB) RETURN JSON_ELEMENT_T
STATIC FUNCTION parse(json BLOB) RETURN JSON_ELEMENT_T

构造函数parse将VARCHAR2/BLOB/CLOB类型的有效json输入转换为JSON_ELEMENT_T类型输出，如果输入的json串无效则会报错。

序列化¶

MEMBER FUNCTION to_String RETURN VARCHAR2
MEMBER FUNCTION to_Number RETURN NUMBER
MEMBER FUNCTION to_Boolean RETURN BOOLEAN
MEMBER FUNCTION to_Clob RETURN CLOB
MEMBER FUNCTION to_Blob RETURN BLOB

MEMBER PROCEDURE to_Clob(c IN OUT CLOB)
MEMBER PROCEDURE to_Blob(c IN OUT BLOB)

序列化函数将JSON_ELEMENT_T中的json数据转换为对应的类型并输出，序列化过程to_Clob和to_Blob将输入JSON_ELEMENT_T中的json数据转换为对应的数据并存入输入的lob对象中。

内省函数¶

MEMBER FUNCTION is_Object RETURN BOOLEAN
MEMBER FUNCTION is_Array RETURN BOOLEAN
MEMBER FUNCTION is_Number RETURN BOOLEAN
MEMBER FUNCTION is_String RETURN BOOLEAN
MEMBER FUNCTION is_Boolean RETURN BOOLEAN
MEMBER FUNCTION is_True RETURN BOOLEAN
MEMBER FUNCTION is_False RETURN BOOLEAN
MEMBER FUNCTION is_Null RETURN BOOLEAN
MEMBER FUNCTION get_Size RETURN NUMBER

内省函数可以查看json对象的属性，其中get_Size函数的返回值取决于json类型：

JSON_SCALAR_T：返回1
JSON_OBJECT_T：返回对象的属性个数
JSON_ARRAY_T：返回数组的长度

注解

JSON_OBJECT_T、JSON_ARRAY_T作为JSON_ELEMENT_T的子类型，继承了JSON_ELEMENT_T的所有函数，如to_String、is_Number、is_String等。
神通的JSON_SCALAR_T是一个空类型，只包含json值和返回1的get_Size函数。

示例1¶

-- json_element_t构造函数
create or replace procedure proc1 as
	je json_element_t;
	str varchar2(50);
begin
	je:=json_element_t.parse('12345'::varchar2);
	dbms_output.put_line(je.to_Number);
	je:=json_element_t.parse('{"a":"xyz"}'::clob);
	dbms_output.put_line(je.to_String);
	je:=json_element_t.parse('false');
	dbms_output.put_line(je.to_Boolean);
	je:=json_element_t.parse('[1,2,3,4]');
	dbms_output.put_line(je.get_Size);
	je:=json_element_t.parse('12345');
	str:=dbms_lob.substr(je.to_Clob,5,1);
	dbms_output.put_line(str);
end;

/
exec proc1;
12345
{"a": "xyz"}
f
4
12345
drop procedure proc1;

示例2¶

--treat as json_object_t
create or replace procedure proc1 as
	je json_element_t;
	jo json_object_t;
begin
	je:=json_element_t('{"a":"xyz","b":[1,2,3]}');
	jo:=treat(je as json_object_t);
	dbms_output.put_line(jo.to_String);
	jo:=treat(json_element_t('{"a":"xyz"}') as json_object_t);
	dbms_output.put_line(jo.to_String);
end;

/
exec proc1;
{"a": "xyz", "b": [1, 2, 3]}
{"a": "xyz"}
drop procedure proc1;

--treat as json_array_t
create or replace procedure proc1 as
	je json_element_t;
	ja json_array_t;
begin
	je:=json_element_t('[1,2,true,"abcd",{"a":"xyz"}]');
	ja:=treat(je as json_array_t);
	dbms_output.put_line(ja.to_String);
	je:=ja.get(4);
	dbms_output.put_line(je.to_String);
	ja:=treat(json_element_t('[1,2,3,4]') as json_array_t);
	dbms_output.put_line(ja.to_String);
end;

/
exec proc1;
[1, 2, true, "abcd", {"a": "xyz"}]
{"a": "xyz"}
[1, 2, 3, 4]
drop procedure proc1;