JSON型データを操作しやすくするために、OceanBaseデータベースはPLステートメントで JSON_OBJECT_T、JSON_ELEMENT_T、JSON_ARRAY_T の3種類のJSONデータ型をサポートしています。
静的関数 parse は、VARCHAR2、CLOB、または BLOB 型のインスタンスを入力パラメータとして受け取り、それをJSONテキストに解析して、JSON_ELEMENT_T または JSON_OBJECT_T 型のインスタンスを返します。
機能の適用範囲
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionはMySQLモードのみを提供します。
JSON_ELEMENT_Tオブジェクトタイプ
JSON_ELEMENT_Tは、JSONテキストを解析してJSON_ELEMENT_T型のインスタンスを構築します。
JSON_ELEMENT_Tインスタンスの作成
parse 関数を使用して JSON_ELEMENT_T のインスタンスを作成できます。parse 関数で JSON_ELEMENT_T インスタンスを作成する際、この関数は VARCHAR2、CLOB、または BLOB データをJSON文字列として入力し、JSON_ELEMENT_T インスタンスを返します。提供された入力が有効なJSONではない場合、エラーが発生します。入力制約として IS JSON SQL条件を使用できます。
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
空の JSON_ELEMENT_T インスタンスは作成できません。サブタイプのいずれかに基づいて、空のJSONコンテナを作成できます。
JSONオブジェクトタイプのシリアライズ
シリアライズは parse 関数の逆プロセスであり、入力されたJSONデータを解析して文字列を返します。シリアライズメソッドの名前は to_ プレフィックスで始まります。ほとんどのシリアライズメソッドはメンバーメソッドです。ただし、CLOB または BLOB インスタンスのシリアライズには、メンバーメソッドとメンバープロシージャの2種類の形式があります。
MEMBER FUNCTION to_String RETURN VARCHAR2
MEMBER FUNCTION to_Number RETURN NUMBER
MEMBER FUNCTION to_Date RETURN DATE
MEMBER FUNCTION to_Timestamp RETURN TIMESTAMP
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オブジェクトのプロパティのクエリ
以下の関数を使用すると、JSONオブジェクトを変更することなくそのプロパティをクエリできます。
MEMBER FUNCTION is_Object RETURN BOOLEAN
MEMBER FUNCTION is_Array RETURN BOOLEAN
MEMBER FUNCTION is_Scalar RETURN BOOLEAN
MEMBER FUNCTION is_String RETURN BOOLEAN
MEMBER FUNCTION is_Number 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 is_Date RETURN BOOLEAN
MEMBER FUNCTION is_Timestamp RETURN BOOLEAN
MEMBER FUNCTION get_Size RETURN NUMBER
get_size 関数の戻り値はJSONタイプによって異なります：
- スカラーの場合、1を返します。
- オブジェクトの場合、keyの数を返します。
- 配列の場合、要素の数を返します。
JSON_OBJECT_Tオブジェクト型
サブタイプ JSON_OBJECT_T はJSONオブジェクトに使用されます。JSON_OBJECT_T には同名のコンストラクタがあり、これを使用して空のJSONオブジェクトを作成し、必要に応じてオブジェクトメンバーを追加できます。
JSONオブジェクトの構築
以下のコンストラクタを使用して、空の JSON_OBJECT_T オブジェクトを作成します：
CONSTRUCTOR FUNCTION JSON_OBJECT_T RETURN SELF AS RESULT
以下の parse 関数を使用して JSON_OBJECT_T インスタンスを作成します。この関数はJSON文字列を入力として受け取り、提供された入力が有効なJSONでない場合はエラーが発生します。
STATIC FUNCTION parse(json VARCHAR2) RETURN JSON_OBJECT_T
STATIC FUNCTION parse(json CLOB) RETURN JSON_OBJECT_T
STATIC FUNCTION parse(json BLOB) RETURN JSON_OBJECT_T
以下の関数を使用して JSON_OBJECT_T インスタンスを作成することもできます：
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn VARCHAR2) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn CLOB) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION JSON_OBJECT_T(jsn BLOB) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION JSON_OBJECT_T(e JSON_ELEMENT_T) RETURN SELF AS RESULT
JSONオブジェクトの値の取得
以下の関数とプロシージャを使用して、JSONオブジェクトの値を取得できます。
get(key VARCHAR2) RETURN JSON_ELEMENT_T // 基底クラス型を返すかどうかは状況に応じて判断する
get_String(key VARCHAR2) RETURN VARCHAR2 // 取得したvalueをVARCHAR2に変換
get_Number(key VARCHAR2) RETURN NUMBER // 取得したvalueをNUMBERに変換
get_Date(key VARCHAR2) RETURN DATE // 取得したvalueをDATEに変換
get_Timestamp(key VARCHAR2) RETURN TIMESTAMP // 取得したvalueをTIMESTAMPに変換
get_Boolean(key VARCHAR2) RETURN BOOLEAN // 取得したvalueをBOOLEANに変換
get_Clob(key VARCHAR2) RETURN CLOB // 取得したvalueをCLOBに変換
get_Blob(key VARCHAR2) RETURN BLOB // 取得したvalueをBLOBに変換
get_Object(key VARCHAR2) RETURN JSON_OBJECT_T // 取得したvalueをJSON_OBJECT_Tに変換
JSONオブジェクトの値を設定する
以下の手順でJSONオブジェクトの値を設定できます。既存の値がある場合、その値は上書きされます。
PROCEDURE get_Clob(key NUMBER, c IN OUT CLOB) // 検索したvalueをCLOBに変換し、cパラメータに格納する
PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB) // 検索したvalueをBLOBに変換し、cパラメータに格納する
PROCEDURE put(key VARCHAR2, value JSON_ELEMENT_T) // K-Vペアを追加する。valueの型はJSON_ELEMENT_Tで、基底クラスを設定するかどうかは状況に応じて決定する
PROCEDURE put(key VARCHAR2, value VARCHAR2) // K-Vペアを追加する。valueの型はVARCHAR2である
PROCEDURE put(key VARCHAR2, value NUMBER) // K-Vペアを追加する。valueの型はNUMBERである
PROCEDURE put(key VARCHAR2, value BOOLEAN) // K-Vペアを追加する。valueの型はBOOLEANである
PROCEDURE put(key VARCHAR2, value DATE) // K-Vペアを追加する。valueの型はDATEである
PROCEDURE put(key VARCHAR2, value TIMESTAMP) // K-Vペアを追加する。valueの型はTIMESTAMPである
PROCEDURE put_NULL(key VARCHAR2) // K-Vペアを追加する。valueの型はnullである
JSONオブジェクトのプロパティのクエリ
以下の関数を使用すると、JSONオブジェクトを変更することなくそのプロパティをクエリできます。
has(key VARCHAR2) RETURN BOOLEAN // 存在するかどうか。json_exist(json_doc, "$.key")に相当します。
get_Type(key VARCHAR2) RETURN VARCHAR2 // json_query(json_doc, "$.key.type()")に相当します。
JSON_OBJECT_T の使用例は以下のとおりです：
# parse
declare
jo JSON_OBJECT_T;
jo_val BLOB;
begin
jo := JSON_OBJECT_T.parse('{"a":"xyz"}');
jo_val := jo.get_Blob('a');
dbms_output.put_line(utl_raw.cast_to_varchar2(jo_val));
end;
/
# parse
declare
jo JSON_OBJECT_T;
lob CLOB := EMPTY_CLOB();
begin
jo := JSON_OBJECT_T.parse('{"a":"xyz"}');
jo.get_Clob('a', lob);
dbms_output.put_line(lob);
end;
/
# put/get メソッド
declare
jo JSON_OBJECT_T;
je JSON_ELEMENT_T;
begin
jo := JSON_OBJECT_T;
jo.put('a', false);
je := jo.get('a');
if (je.is_False) THEN
dbms_output.put_line('is_False');
END IF;
end;
/
# put/get メソッド
declare
jo JSON_OBJECT_T;
je JSON_ELEMENT_T;
jo_val TIMESTAMP;
begin
jo := JSON_OBJECT_T;
jo_val := '2023-01-02 10:10:32.000000';
jo.put('a', jo_val);
je := jo.get('a');
if (je.is_Timestamp) THEN
dbms_output.put_line('is_Timestamp');
END IF;
end;
/
# put/get/to_string/parse の使用
declare
jo_src JSON_OBJECT_T;
jo_dst JSON_OBJECT_T;
begin
jo_src := JSON_OBJECT_T.parse('{"a":"xyz"}');
jo_dst := jo_src.clone;
jo_src.put('b', 'mnl');
jo_dst.put('c', 'hik');
dbms_output.put_line(jo_src.to_String);
dbms_output.put_line(jo_dst.to_String);
end;
/
JSON_ARRAY_Tオブジェクト型
JSON_ARRAY_TはJSON_ELEMENT_Tのサブタイプであり、JSONデータ内のArray型を表すために使用されます。
JSON_ARRAY_Tには主に静的関数(STATIC FUNCTION)、メンバー関数(MEMBER FUNCTION)、コンストラクタ関数(CONSTRUCTOR FUNCTION)が含まれます。
使用方法
JSON_ARRAY_Tは5つのコンストラクタを提供しており、ユーザーは簡単にJSON_ARRAY_Tオブジェクトを作成できます。これらのコンストラクタは、CLOB、VARCHAR2、その他のJSON要素型を含むさまざまなパラメータ型をサポートしています。
CONSTRUCTOR FUNCTION JSON_ARRAY_T RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_ARRAY_T(o JSON_ELEMENT_T) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_ARRAY_T(o JSON_ARRAY_T) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_ARRAY_T(jsn VARCHAR2) RETURN SELF AS RESULT
CONSTRUCTOR FUNCTION JSON_ARRAY_T(jsn CLOB) RETURN SELF AS RESULT
JSON_ARRAY_T型のJSON配列では、各要素には一意のインデックスが対応します。getメソッドを使用すると、ユーザーは配列のインデックスを通じて特定の位置の要素を取得し、JSON配列内の各要素を走査およびアクセスすることができます。
MEMBER FUNCTION get(pos NUMBER) RETURN JSON_ELEMENT_T
JSON_ARRAY_Tはget_size()とget_Type(pos NUMBER)という自己参照メソッドを提供しています。get_size()メソッドは配列要素の数を取得するために使用され、get_Type(pos NUMBER)メソッドは指定された位置の要素のデータ型を取得するために使用されます。
MEMBER FUNCTION get_Type(pos NUMBER) RETURN VARCHAR2
MEMBER FUNCTION get_size RETURN NUMBER
JSON_ARRAY_Tの使用例は以下のとおりです：
JSON_ARRAY_T関数の構築例
以下の例では、コンストラクタを使用してJSON_ARRAY_Tオブジェクトを作成します。
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T('[123]');
DBMS_OUTPUT.PUT_LINE(jo.to_String);
END;
/
インデックスによる要素の取得例
以下のSQLは、JSON文字列の解析を例に、getメソッドを使用してインデックスから配列要素を取得する方法を示しています。
DECLARE
jo JSON_ARRAY_T;
jo_val JSON_ELEMENT_T;
BEGIN
jo := JSON_ARRAY_T.parse('[123,{"abc":456},[789], true, null,"test"]');
FOR I IN 0 .. 5 LOOP
jo_val := jo.get(i);
DBMS_OUTPUT.PUT_LINE(jo_val.to_String);
END LOOP;
END;
/
要素数の集計例
以下のSQLは、JSON文字列の解析を例に、get_sizeメソッドを使用して配列要素の数を集計し、ループを使用して各要素を取得する方法を示しています。
declare
jo JSON_ARRAY_T;
jo_val JSON_ELEMENT_T;
begin
jo := JSON_ARRAY_T.parse('[123,{"abc":456},[789], true, null,"test"]');
FOR I IN 0 .. jo.get_size-1 LOOP
jo_val := jo.get(i);
dbms_output.put_line(jo_val.to_String);
END LOOP;
end;
/
JSON_ARRAY_APPEND オブジェクトタイプ
appendは主に、現在のJSON_ARRAY_Tオブジェクトの末尾に単一のJSON要素を追加し、配列オブジェクト自体を直接変更するために使用されます。
構文
MEMBER PROCEDURE append(value JSON_ELEMENT_T)
MEMBER PROCEDURE append(value VARCHAR2)
MEMBER PROCEDURE append(value NUMBER)
MEMBER PROCEDURE append(value BOOLEAN)
MEMBER PROCEDURE append(value DATE)
MEMBER PROCEDURE append(value TIMESTAMP)
MEMBER PROCEDURE append(value BLOB)
MEMBER PROCEDURE append(value CLOB)
MEMBER PROCEDURE append(value JSON)
ここで、valueは現在のJSON_ARRAY_Tオブジェクトに追加する単一のJSON要素を指します。
例
declare
jo_src JSON_ARRAY_T;
begin
jo_src := JSON_ARRAY_T();
jo_src.append('"OceanBase"');
jo_src.append('OceanBase');
jo_src.append('海洋データベース');
朱应军
jo_src.append(JSON_ARRAY_T('[1,2,3]'));
jo_src.append(JSON_OBJECT_T('{"David Tao" : 1}'));
jo_src.append(true);
jo_src.append(1);
jo_src.append(1.1);
jo_src.append(-4294967296);
jo_src.append(18446744073709551616);
dbms_output.put_line(jo_src.to_String);
end;
/
JSON_ARRAY_APPEND_NULL オブジェクトタイプ
append_nullは、現在のJSON配列にNull値を追加し、配列オブジェクト自体を直接変更します。入力パラメータも戻り値もありません。
構文
MEMBER PROCEDURE append_Null
例
DECLARE
jo JSON_ARRAY_T;
BEGIN
jo := JSON_ARRAY_T;
jo.append_Null;
jo.append_Null;
dbms_output.put_line(jo.to_String);
END;
/
SELECT println() FROM dual;
/
JSON_ARRAY_APPEND_ALLオブジェクト型
append_allは、JSON要素型（JSON配列、JSONオブジェクト、または単一のJSON要素のいずれか）を現在のJSON_ARRAY_Tオブジェクトに追加します（挿入される要素の重複処理をサポートします）。
構文
MEMBER PROCEDURE append_all(arr JSON_ARRAY_T, excl_exis BOOLEAN DEFAULT FALSE)
ここで：
arr JSON_ARRAY_T: このパラメータは、複数の子要素を含むJSON配列です。excl_exis:このパラメータはブール値であり、既存の要素を除外するかどうかを制御します。デフォルト値はFALSEで、これはすべての要素を対象配列に追加し、要素が既に存在するかどうかをチェックしないことを意味します。TRUEに設定すると、対象配列にまだ存在しない要素のみを追加します。
例
excl_exisのデフォルト値はFALSEで、すべての要素を対象配列に追加し、要素が既に存在するかどうかをチェックしないことを意味します：
DECLARE
jo JSON_ARRAY_T;
je JSON_ELEMENT_T;
js json_object_t:=json_object_t('{"key": "OceanBase"}');
BEGIN
jo := JSON_ARRAY_T('[1,2,"OceanBase"]');
dbms_output.put_line(jo.to_String);
je :=js.get('key');
jo.append_all(je); -- excl_exisのデフォルト値はfalse
dbms_output.put_line(je.to_String);
dbms_output.put_line(jo.to_String);
END;
/
SELECT PRINTLN() FROM dual;
/
excl_exisをTRUEに設定すると、対象配列にまだ存在しない要素のみを追加します：
DECLARE
jo JSON_ARRAY_T;
je JSON_ELEMENT_T;
js json_object_t:=json_object_t('{"key": "OceanBase"}');
BEGIN
jo := JSON_ARRAY_T('[1,2,"OceanBase"]');
dbms_output.put_line(jo.to_String);
je :=js.get('key');
jo.append_all(je, true);
dbms_output.put_line(je.to_String);
dbms_output.put_line(jo.to_String);
END;
/