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を返します。
- オブジェクトの場合、キーの数を返します。
- 配列の場合、要素数を返します。
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) // 検索した値をCLOBに変換し、cパラメータに格納
PROCEDURE get_Blob(key NUMBER, c IN OUT BLOB) // 検索した値を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データ内の配列型を表すために使用されます。
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;
/