25.2 SQL/JSON生成関数の入力値の処理

SQL/JSON生成関数は、入力としてSQL値を受け取り、JSONオブジェクトまたは配列を返します。入力値を使用して、JSONオブジェクトのフィールド値のペアまたはJSON配列の要素を生成します。入力値を使用する方法は、SQLデータ型によって異なります。

返されるJSONオブジェクトまたは配列は、JSONデータをサポートするSQLデータ型です(JSON、VARCHAR2、CLOBまたはBLOB)。デフォルトの戻り型はVARCHAR2(4000)です。すべての場合に、戻り値には整形式のJSONデータが含まれていることをデータベースが認識します。

JSONデータ型以外の場合、入力の後にキーワードFORMAT JSONを指定できます。このキーワードは、値がすでにJSONデータを表しているとみなされるように宣言するため(ユーザーがそれを保証します)、JSONデータとして解釈(解析)されます。

たとえば、入力が'{}'の場合は、JSON文字列"{}"ではなく、空のJSONオブジェクト{}を生成しようとしていることもあります。例25-1は、FORMAT JSONを使用して、入力のVARCHAR2文字列"{\"x\":5}"によってJSONオブジェクト{"x":5}を生成する例を示しています。

入力データがJSON型の場合は、そのまま使用されます。これには、JSON型コンストラクタが使用される場合も含まれます。(この場合、FORMAT JSONを使用しないでください。使用するとエラーが発生します。)

入力がJSON型ではなく、FORMAT JSONを使用しない場合でも、Oracleでは結果がJSONデータであることを認識します。その場合、FORMAT JSONの使用は必須ではなく、任意です。これは、たとえば、入力データがファンクションjson_queryまたはJSON生成関数のいずれかを使用した結果である場合です。

なんらかの方法で入力がJSONデータと認識されている場合、その入力は基本的にそのまま使用されて、結果が構成されます(処理は不要です)。これは入力がJSONスカラー、オブジェクトまたは配列のいずれを表すかにかかわらず適用されます。

入力がJSONデータであると認識されない場合は、次のようなJSON値が生成されます(他のSQL値の場合はエラーが発生します)。

  • ユーザー定義のSQLオブジェクト型のインスタンスの場合は、フィールド名がオブジェクト属性名から取得され、フィールド値が(JSON生成が再帰的に適用される)オブジェクト属性値から取得されるJSONオブジェクトが生成されます。

  • SQLコレクション型のインスタンスの場合は、(JSON生成が再帰的に適用される)コレクション要素の値から要素の値が取得されるJSON配列が生成されます。

  • VARCHAR2、CLOBまたはNVARCHAR値は、二重引用符(")で囲まれ、JSON標準のJSON文字列に準拠するように、必要に応じて文字がエスケープされます。たとえば、SQL入力'{}'の場合は、JSON文字列"{}"が生成されます。

  • 数値の場合は、JSON数値が生成されます。

    データベース初期化パラメータcompatibleが20以上の場合は、NUMBERの入力によってJSONのnumber値、BINARY_DOUBLEの入力によってJSONのdouble値、BINARY_FLOATの入力によってJSONのfloat値が生成されます。

    compatibleが20未満の場合は、数値の入力型(NUMBER、BINARY_DOUBLEまたはBINARY_FLOAT)に関係なく、値はJSONのnumberになります。

    正の無限大および負の無限大の数値、および数値演算の未定義の結果である値(非数値、つまりNaN)は、JSON数値として表すことができません。これらはかわりにJSON文字列を生成します(それぞれ"Inf"、"-Inf"および"Nan")。

  • RAWまたはBLOB値の場合は、二重引用符(")で囲まれた16進のJSON文字列が生成されます。

  • 時間に関連した値(DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE、INTERVAL YEAR TO MONTHまたはINTERVAL DAY TO SECOND)の場合は、サポートされているISO 8601形式が生成され、その結果はJSON文字列として二重引用符(")で囲まれます。

  • BOOLEAN (SQLまたはPL/SQL)値のTRUEまたはFALSEは、それぞれJSONのtrueまたはfalseを生成します。

  • SQLのNULL値の場合は、NULLデータ型に関係なく、JSONのnullが生成されます。

  • SQL VECTOR値は、ベクター型のJSON言語スカラー値を生成します。

ノート:

SQL/JSON生成関数へのデータ型CLOBおよびBLOBの入力の場合、空のインスタンスはSQLのNULLと区別されます。空のJSON文字列("")が生成されます。ただし、データ型VARCHAR2、NVARCHAR2およびRAWの入力の場合、Oracle SQLでは空の値(長さがゼロ)をNULLとして扱うため、そのような値がJSON文字列に生成されると想定しないでください。

例25-1 FORMAT JSON: 入力のSQL値をJSONデータにする宣言

この例では、PL/SQLファンクションgetX()は、JSONオブジェクトを表すVARCHAR2値を返します。FORMAT JSONが使用されていない場合に生成関数json_arrayによって返される値は、JSON オブジェクトの[ {"x":5} ]ではなく、文字列要素のJSONの単一の配列[ "{\"x\":5} "]になります。 

-- PL/SQL: Return a SQL string representing a JSON object
CREATE FUNCTION getX(n NUMBER) RETURN VARCHAR2 AS
BEGIN
  RETURN '{"x":'|| n ||'}';
END;
-- SQL: Generate JSON data from SQL
SELECT json_array(getX(5) FORMAT JSON) FROM DUAL;

FORMAT JSONを使用しても、入力が整形式のJSONデータである保証はありません。そのようにする必要がある場合は、生成ファンクションを使用する際に、キーワードSTRICTを含めます。 

SELECT json_array(getX(5) FORMAT JSON STRICT) FROM DUAL;

関連項目:

Oracleの組込みデータ型

関連トピック

親トピック: SQLを使用したJSONデータの生成