24 SQLを使用したJSONデータの生成
SQLを使用すると、データベース内のJSON以外のデータからJSONオブジェクトおよび配列を生成できます。これを行うには、コンストラクタJSONまたはSQL/JSONファンクションjson_object、json_array、json_objectaggおよびjson_arrayaggを使用します。
- JSON生成の概要
JSONデータの生成の概要について説明します(ベスト・プラクティス、SQL/JSON生成関数、単純なJSONコンストラクタ構文、入力SQL値の処理、および生成されるデータ)。 - SQL/JSON生成関数の入力値の処理
SQL/JSON生成関数は、入力としてSQL値を受け取り、JSONオブジェクトまたは配列を返します。入力値を使用して、JSONオブジェクトのフィールド値のペアまたはJSON配列の要素を生成します。入力値を使用する方法は、SQLデータ型によって異なります。 - SQL/JSONファンクションJSON_OBJECT
SQL/JSONファンクションjson_objectは、引数のSQL式を評価した結果からJSONオブジェクトを構成します。 - SQL/JSONファンクションJSON_ARRAY
SQL/JSONファンクションjson_arrayは、引数のSQL式を評価した結果からJSON配列を構成します。 - SQL/JSONファンクションJSON_OBJECTAGG
SQL/JSONファンクションjson_objectaggは、グループ化されたSQL問合せの複数行の情報をオブジェクト・メンバーとして集計して、JSONオブジェクトを構成します。 - SQL/JSONファンクションJSON_ARRAYAGG
SQL/JSONファンクションjson_arrayaggは、グループ化されたSQL問合せの複数行の情報を配列要素として集計して、JSON配列を構成します。配列要素の順序は、デフォルトでは、問合せ結果の順序に反映されますが、ORDER BY句を使用すると、配列要素の順序を指定できます。 - JSON生成に基づく読取り専用ビュー
JSON生成関数またはコンストラクタJSONを使用して読取り専用ビューを作成できます。アクセス権があるユーザーは誰でも、そのビューを読取り専用表であるかのように使用できます。JSONデータのユーザーは、それがそれ自体として格納されているかや必要に応じて生成されているかを把握する必要も気にする必要もありません。
親トピック: JSONデータの生成
24.1 JSONにおける生成の概要
JSONデータの生成の概要について説明します(ベスト・プラクティス、SQL/JSON生成関数、単純なJSONコンストラクタ構文、入力SQL値の処理、および生成されるデータ)。
データベースのJSON以外のデータからJSONデータを生成するための最適な方法は、SQLを使用する方法です。標準SQL/JSON関数json_object、json_array、json_objectaggおよびjson_arrayaggは、このために設計されています。生成されるデータがJSON型の場合は、JSONデータ型のコンストラクタ関数JSONを使用することが便利な代替の方法です。
どちらの方法を使用しても、SQL問合せからJSONデータを直接簡単に作成できます。非JSONデータをJSONオブジェクトおよびJSON配列として表すことが可能になります。生成関数またはコンストラクタJSONへの呼出しをネストすると、複雑で階層化されたJSON文書を生成できます。ネストされた副問合せにより、1対多関係を表すJSONデータを生成できます。脚注1
非JSONデータからJSONデータを構成するための最適な方法
SQL/JSON生成関数の使用のかわりとなる方法は、エラーが発生しやすかったり、非効率的であることが一般的です。
-
文字列の連結を使用したJSON文書の生成では、エラーが発生しやすくなります。特に、二重引用符(
")などの特殊文字をいつ、どのようにエスケープするかという点について、順守する必要がある複雑なルールが多数あります。これらのルールは、見逃されたり誤って解釈されることが多く、その結果、正しくないJSONデータが生成される可能性が生じます。 -
非JSONの結果セットをデータベースから読み取り、クライアント側のアプリケーション・コードを使用してJSONデータを生成する方法は、特にネットワーク・オーバーヘッドが原因で非常に効率が悪くなることが一般的です。1対多関係をJSONデータとして表す場合は、必要な非JSONデータをすべて収集するために、複数の
SELECT操作が必要になることが多くなります。生成する文書で複数のレベルの1対多関係が示される場合は、この方法では必要なコストが増えることが考えられます。
SQL/JSON生成関数およびコンストラクタJSONでは、このような問題が発生しません。非JSONデータベース・データからJSONデータを構成するジョブ向けに設計されています。
-
これらの関数では、常に整形式のJSON文書が構成されます。
-
関数でSQL副問合せを使用すると、1つのSQL文を使用してJSON文書一式を生成できるため、生成処理を最適化できます。
-
クライアントに戻されるのは、生成された文書だけなので、ネットワーク・オーバーヘッドは最小化されます。生成される文書当たり、多くても1往復で済みます。
SQL/JSONの生成関数
-
ファンクション
json_objectおよびjson_arrayは、それぞれJSONオブジェクトまたは配列を構成します。最も単純なケースでは、json_objectは引数としてSQL名前/値ペアを受け取り、json_arrayは引数としてSQL値を受け取ります。 -
関数
json_objectaggおよびjson_arrayaggは、集計のSQLファンクションです。これらの関数は、グループ化されたSQL問合せの行に格納された情報を、それぞれ、JSONオブジェクトと配列に変換します。引数の評価によって、オブジェクト・メンバーと配列要素の数が決まります。つまり、結果のサイズは現在の問合せ対象データを反映します。json_objectaggおよびjson_arrayaggの場合、オブジェクト・メンバーと配列要素の順序は、指定されません。json_arrayaggの場合、json_arrayaggの呼出し内でORDER BY句を使用すると、配列要素の順序を制御できます。
SQL/JSON生成関数によって戻される結果
デフォルトでは、生成されたJSONデータは生成関数からSQL VARCHAR2(4000)値として戻されます。オプションのRETURNING句を使用して、異なるVARCHAR2サイズを指定するか、かわりにJSON、CLOBまたはBLOBの戻り値を指定できます。BLOBが戻り型の場合、文字セットはAL32UTF8です。
戻り型がJSONである場合を除き、入力SQL値から生成されるJSON値はテキストのJSONにシリアライズされます。このシリアライズは、Oracle SQLファンクションjson_serializeと同じ効果があります。
ノート:
Oracle SQLファンクションjson_serializeは、標準書式を使用して、Oracle JSON言語スカラー型(浮動小数点や日付など)の値を一貫してシリアライズします。
たとえば、ISO 8601日付書式YYYY-MM-DDを使用してJSON日付値をシリアライズします。
他の形式のJSON文字列値を含むテキストJSONオブジェクトまたは配列を生成する場合は、その形式の文字列を生成関数への入力として指定します。(json_serializeへの文字列入力は、そのまま出力されます。)
たとえば、json_objectで、異なるISO 8601日付書式の文字列フィールドを持つオブジェクトを生成する場合は、to_charなどのSQL変換関数を使用してその文字列を指定し、それをjson_objectに渡します。
SQL/JSON生成関数の入力値の処理
SQL/JSON生成関数は入力としてSQLの値を受け取り、返されるJSONオブジェクトまたは配列内にJSON値を生成します。出力に使用されるJSON値が入力値からどのように生成されるかは、それらのSQLデータ型によって異なります。
SQL/JSON生成関数のオプションの動作
オプションとして、SQL NULLを処理する句、RETURNING句およびキーワードSTRICTおよびWITH UNIQUE KEYSを指定できます。
-
NULLを処理する句—入力評価の結果のSQLNULL値をどのように扱うかを決定します。-
NULL ON NULL—入力のSQLNULL値が、出力のJSONnullに変換されます。これは、json_objectとjson_objectaggのデフォルト動作です。 -
ABSENT ON NULL—入力のSQLNULL値の結果、対応する出力がない状態になります。これは、json_arrayとjson_arrayaggのデフォルト動作です。
-
-
RETURNING句—関数の戻り値に使用されるSQLデータ型です。戻り型は、JSONデータをサポートする任意のSQL型にすることができます(JSON、VARCHAR2、CLOBまたはBLOB)。デフォルトの戻り型(RETURNING句なし)はVARCHAR2(4000)です。 -
STRICTキーワード—指定すると、戻されるJSONデータが整形式になっているかチェックされます。STRICTを指定して戻されたデータが整形式でない場合には、エラーが発生します。ノート:
通常、
JSONデータ型のデータの生成時にSTRICTを指定する必要はなく、これを行うと若干のパフォーマンス・ペナルティが生じます。入力データおよび返されるデータの両方が
JSON型の場合、STRICTを指定しないと、その入力は返されるデータにそのまま使用されます。厳密な整形式としてチェックされません。(1)入力データも
JSON型であり、(2)それが完全に厳密ではない可能性がある場合、JSON型のデータを返すときにSTRICTを使用できます。たとえば、クライアント・アプリケーションで入力データが作成され、各JSON文字列が有効なUTF-8のバイト・シーケンスで表されていることが保証されない場合などです。 -
WITH UNIQUE KEYSキーワード(テキストJSONデータの生成時) — 存在する場合は、返されたJSONオブジェクトがチェックされて、重複するフィールド名がないことが確認されます。重複がある場合は、エラーが発生します。これらのキーワードは、json_objectおよびjson_objectaggの場合のみ使用できます。テキストJSONデータの生成時に、
WITH UNIQUE KEYSが存在しない場合(またはWITHOUT UNIQUE KEYSが存在する場合)、フィールドが一意かどうかのチェックは実行されません。その場合は、重複するものを含め、すべてのフィールドが使用されます。WITH[OUT] UNIQUE KEYSは、JSON型のデータを生成する場合には効果がありません。戻り型がJSONである場合は、重複キーがあると必ずエラーが発生します。
JSONデータ型コンストラクタ
データ型がJSONのデータを生成する場合、json_objectおよびjson_arrayを使用するかわりに、特別な構文を持つコンストラクタJSONを使用できます。(コンストラクタJSONおよびJSON型を使用できるのは、データベース初期化パラメータcompatibleが少なくとも20の場合のみです。そうでない場合は、エラーが発生します。)
動作の違いは、コンストラクタを使用したときの戻りデータ型は、常にJSONであるということのみです(コンストラクタにはRETURNING句はありません)。
json_objectまたはjson_arrayの代替構文として使用する場合は、オブジェクトと配列の生成時に、通常のカッコ(())ではなく、中カッコ({})と大カッコ([])をそれぞれ使用して、コンストラクタJSONを直接実行します。
-
JSON { … }はJSON(json_object( … ))と同じ効果があり、後者はjson_object( … RETURNING JSON)と同じ効果があります。 -
JSON [ … ]はJSON(json_array( … ))と同じ効果があり、後者はjson_array( … RETURNING JSON)と同じ効果があります。
json_objectおよびjson_arrayがRETURNING JSONとともに使用される場合のこれらのファンクションの動作と構文のすべての選択肢は、特別な構文を持つコンストラクタJSONを使用する場合にも利用できます。例24-2、例24-3、例24-4、例24-5、例24-6、例24-7および例24-8を参照してください
JSON {…}およびJSON […]は、json_objectおよびjson_arrayのみの代替構文であり、集計生成関数json_objectaggおよびjson_arrayaggには使用できません。ただし、SQL問合せ式は引数としてjson_arrayに渡すことができるため、JSON […]の(単一)引数としても使用できます。たとえば、この2つの問合せは等価です。
SELECT json_arrayagg(department_name)FROM departments;SELECT json_array(SELECT department_name FROM departments) FROM DUAL;さらに、json_objectaggまたはjson_arrayaggへの明示的な呼出しの結果に対して、(特別な構文なしで)コンストラクタJSONを使用することもできます。たとえば、この2つの問合せは等価です。
SELECT JSON(json_objectagg(department_name VALUE department_id))
FROM departments;
SELECT json_objectagg(department_name VALUE department_id
RETURNING JSON)
FROM departments;関連項目:
-
『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス
-
『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス
-
『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス
-
『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンス
-
『Oracle Database SQL言語リファレンス』JSON型コンストラクタに関する項
親トピック: SQLを使用したJSONデータの生成
24.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オブジェクト{}を生成しようとしていることもあります。例24-1は、FORMAT JSONを使用して、入力のVARCHAR2文字列"{\"x\":5}"によってJSONオブジェクト{"x":5}を生成する例を示しています。
同様に、入力の型がJSONでない場合、SQLファンクションtreatにキーワードAS JSONを指定して適用できます。効果は、FORMAT JSONを使用した場合と同じです。
入力データがJSON型の場合は、そのまま使用されます。これには、JSON型コンストラクタが使用される場合も含まれます。(この場合、FORMAT JSONまたはtreat … AS JSONを使用しないでください。使用するとエラーが発生します。)
入力がJSON型ではなく、FORMAT JSONまたはtreat … AS JSONを使用しない場合でも、Oracleは結果がJSONデータであることを認識します。そのような場合、FORMAT JSONまたはtreat … AS 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/JSON生成関数へのデータ型CLOBおよびBLOBの入力の場合、空のインスタンスはSQLのNULLと区別されます。空のJSON文字列("")が生成されます。ただし、データ型VARCHAR2、NVARCHAR2およびRAWの入力の場合、Oracle SQLでは空の値(長さがゼロ)をNULLとして扱うため、そのような値がJSON文字列に生成されると想定しないでください。例24-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;24.3 SQL/JSONファンクションJSON_OBJECT
SQL/JSONファンクションjson_objectは、引数のSQL式を評価した結果からJSONオブジェクトを構成します。
任意の数の引数を指定でき、各引数は次のいずれかです。
-
明示的なフィールド名/値ペア。例:
answer : 42。名前/値ペア引数は、生成されたJSONオブジェクトのオブジェクト・メンバーを指定します(値式がSQL
NULLに評価される場合、およびABSENT ON NULL句が適用される場合を除く)。名前と値はSQL式です。名前式は、SQL文字列に評価される必要があります。値式は、JSONデータ型のSQL値、またはJSON値として表すことができるSQL値に評価される必要があります。名前式と値式は、キーワードVALUEまたはコロン(:)で区切られます。ノート:
一部のクライアント・ドライバは、問合せをデータベースに送信する前に、問合せテキストのスキャンとバインド変数の特定を試みる場合があります。その場合、バインド変数を導入するときに、
json_object内の名前/値セパレータであるコロンが誤解釈されることがあります。この問題を回避するために、キーワードVALUEをセパレータとして使用('Name' VALUE Diderot)するか、単にペアの値部分をカッコで囲みます('Name':(Diderot))。 -
リレーショナル列名。その前に表名または別名、あるいはビュー名の後にドット(
.)が続く場合があります。例:t1.address。この場合、指定されたデータ行について、列名引数で指定されたJSONオブジェクト・メンバーにフィールド名としての列名とフィールド値としての列値が含まれます。
引用符で囲まれているかどうかに関係なく、指定する列名は大/小文字を区別して解釈されます。たとえば、
Emailを列名引数として使用する場合、EMAIL列のデータは、フィールド名Email(EMAILではない)を持つオブジェクト・メンバーの生成に使用されます。 -
表名または別名、あるいはビュー名の後にドットとアスタリスクのワイルドカード(
*)。例:t1.*。(名前や別名には、myschema.t1.*のように、データベース・スキーマ名の接頭辞を付けることができます。)この場合、表またはビューの列はすべて入力として使用されます。それぞれが明示的に指定されたかのように処理されます。特に、列名は大/小文字を区別して解釈されます。
あるいは、json_objectでは、次のいずれかである単一の引数を受け入れます。
-
ユーザー定義のSQLオブジェクト型のインスタンス。例:
json_object(my_sql_object_42)。この場合、結果のJSONオブジェクト・フィールド名はSQLオブジェクト属性名から付けられ、その値は(JSON生成が再帰的に適用される)SQLオブジェクト属性値から取得されます。
-
アスタリスク・ワイルドカード(
*)。例:json_object(*)。ワイルドカードは、表またはビューのすべての列を明示的に指定してオブジェクト・メンバーを生成するためのショートカットとして機能します。結果のJSONオブジェクト・フィールド名は、大文字の列名です。ワイルドカードは、
FROMリストからわかる表、ビューまたは表の別名とともに使用できます。列は任意のSQLデータ型にできます。この場合(
json_object(*))と前述の場合(アスタリスクの前に、明示的な表名またはビュー名(または表の別名)に続いてドットがある:json_object(t.*))の違いに注意してください。json_object(*)の場合、列名は大/小文字を区別して解釈されません。
アスタリスク・ワイルドカードをjson_objectとともに使用する方法を説明するもう1つの方法は、SQL SELECT一覧でワイルドカードに許可されているものに従っているということです。
SQL/JSON条件is jsonと同様に、キーワードSTRICTおよびWITH UNIQUE KEYSはファンクションjson_objectおよびjson_objectaggとともに使用できます。それぞれの動作はis jsonと同じです。
例24-2 JSON_OBJECTでの名前/値ペアの使用
この例では、表hr.employees (標準のデータベース・スキーマHRから)の従業員のうち、給与が15000を超える従業員それぞれのJSONオブジェクトが構成されます。
明示的な名前/値のペアを渡してJSONオブジェクトのメンバーを指定します。このオブジェクトには、フィールドcontactInfoの値として、フィールドmailとphoneのオブジェクトが含められています。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object('id' : employee_id,
'name' : first_name || ' ' || last_name,
'contactInfo' : json_object('mail' : email,
'phone' : phone_number),
'hireDate' : hire_date,
'pay' : salary
RETURNING JSON)
FROM hr.employees
WHERE salary > 15000;
-- The query returns rows such as this (pretty-printed here for clarity):
{"id" : 101,
"name" : "Neena Kochhar",
"contactInfo" : {"mail" : "NKOCHHAR",
"phone" : "515.123.4568"},
"hireDate" : "21-SEP-05",
"pay" : 17000}ノート:
ファンクションjson_objectは常にJSONデータを返すため、入力フィールドcontactInfoの値に対してFORMAT JSONを指定する必要はありません。ただし、そのフィールドの値が'{"mail":' || email ', "phone":' || phone_number || '}'のように指定されていた場合、その後にFORMAT JSONを続けてその文字列値をJSONデータとして解釈する必要があります。
"contactInfo" :
'{"mail":' || email ', "phone":' || phone_number || '}'
FORMAT JSON,JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { 'id' : employee_id,
'name' : first_name || ' ' || last_name,
'contactInfo' : JSON { 'mail' : email,
'phone' : phone_number }
'hireDate' : hire_date,
'pay' : salary }
FROM hr.employees
WHERE salary > 15000;例24-3 JSON_OBJECTでの列名の使用
この例では、employee_idが101である従業員のJSONオブジェクトを構成します。生成されるフィールドには列の名前が付けられ、大/小文字が区別されます。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object(last_name,
'contactInfo' : json_object(email, phone_number),
hire_date,
salary,
RETURNING JSON)
FROM hr.employees
WHERE employee_id = 101;
-- The query returns rows such as this (pretty-printed here for clarity):
{"last_name" : "Kochhar",
"contactInfo" : {"email" : "NKOCHHAR",
"phone_number" : "515.123.4568"},
"hire-date" : "21-SEP-05",
"salary" : 17000}JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { last_name,
'contactInfo' : JSON { email, phone_number },
hire_date,
salary}
FROM hr.employees
WHERE employee_id = 101;例24-4 JSON_OBJECTでのワイルドカード(*)の使用
この例では、給与が15000を超える従業員ごとにJSONオブジェクトを構成します。従業員表の各列は、そのフィールド名が(大文字)列名である1つのオブジェクト・メンバーの構成に使用されます。SQL NULL値によってJSONフィールド値がnullになることに注意してください。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object(* RETURNING JSON)
FROM hr.employees
WHERE salary > 15000;
-- The query returns rows such as this (pretty-printed here for clarity):
JSON_OBJECT(*)
--------------
{"EMPLOYEE_ID":100,
"FIRST_NAME":"Steven",
"LAST_NAME":"King",
"EMAIL":"SKING",
"PHONE_NUMBER":"515.123.4567",
"HIRE_DATE":"2003-06-17T00:00:00",
"JOB_ID":"AD_PRES",
"SALARY":24000,
"COMMISSION_PCT":null,
"MANAGER_ID":null,
"DEPARTMENT_ID":90}
{"EMPLOYEE_ID":101,
"FIRST_NAME":"Neena",
"LAST_NAME":"Kochhar",
"EMAIL":"NKOCHHAR",
"PHONE_NUMBER":"515.123.4568",
"HIRE_DATE":"2005-09-21T00:00:00",
"JOB_ID":"AD_VP",
"SALARY":17000,
"COMMISSION_PCT":null,
"MANAGER_ID":100,
"DEPARTMENT_ID":90}
{"EMPLOYEE_ID":102,
"FIRST_NAME":"Lex",
"LAST_NAME":"De Haan",
"EMAIL":"LDEHAAN",
"PHONE_NUMBER":"515.123.4569",
"HIRE_DATE":"2001-01-13T00:00:00",
"JOB_ID":"AD_VP",
"SALARY":17000,
"COMMISSION_PCT":null,
"MANAGER_ID":100,
"DEPARTMENT_ID":90}JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { * }
FROM hr.employees
WHERE salary > 15000;例24-5 ABSENT ON NULLを指定したJSON_OBJECTの使用
この例では、標準のデータベース・スキーマHRの表hr.locationsに対する問合せを実行して、フィールドcityとprovinceが含まれるJSONオブジェクトを作成します。
json_objectに対するNULL処理のデフォルト動作は、NULL ON NULLです。
JSON値nullが含まれるフィールドが作成されないようにするために、この例ではABSENT ON NULLを使用します。列cityの値が'Singapore'のときに列state_provinceがSQL値NULLになるということは、その場所に対してprovinceフィールドが作成されていないことを意味します。
SELECT JSON_OBJECT('city' : city,
'province' : state_province ABSENT ON NULL)
FROM hr.locations
WHERE city LIKE 'S%';
JSON_OBJECT('CITY'ISCITY,'PROVINCE'ISSTATE_PROVINCEABSENTONNULL)
----------------------------------------------------------------
{"city":"Southlake","province":"Texas"}
{"city":"South San Francisco","province":"California"}
{"city":"South Brunswick","province":"New Jersey"}
{"city":"Seattle","province":"Washington"}
{"city":"Sydney","province":"New South Wales"}
{"city":"Singapore"}
{"city":"Stretford","province":"Manchester"}
{"city":"Sao Paulo","province":"Sao Paulo"}
この例にはRETURNING句がないため、JSONデータはデフォルトでVARCHAR2(4000)として返されます。RETURNING JSONを使用した場合は、問合せにこの代替の構文を使用できます。
SELECT JSON {'city' : city,
'province' : state_province ABSENT ON NULL}
FROM hr.locations
WHERE city LIKE 'S%';例24-6 JSON_OBJECTでのユーザー定義のオブジェクト型インスタンスの使用
この例では、オブジェクト型がshipping_tの列shippingを使用して表po_shipを作成します。(SQL/JSONファンクションjson_valueを使用してJSONデータからshipping_tインスタンスを構築します。例19-5を参照)。
次に、json_objectを使用して、po_ship.shipping列にあるSQLオブジェクト型インスタンスからJSONオブジェクトを生成し、JSONデータ型のインスタンスとして返します。
(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)
CREATE TABLE po_ship
AS SELECT json_value(po_document, '$.ShippingInstructions'
RETURNING shipping_t)
shipping
FROM j_purchaseorder;
DESCRIBE po_ship;
Name Null? Type
--------- ------- ----------
SHIPPING SHIPPING_T
SELECT json_object(shipping RETURNING JSON)
FROM po_ship;
JSON_OBJECT(SHIPPING)
---------------------
{"NAME":"Alexis Bull",
"ADDRESS":{"STREET":"200 Sporting Green",
"CITY":"South San Francisco"}}
{"NAME":"Sarah Bell",
"ADDRESS":{"STREET":"200 Sporting Green",
"CITY":"South San Francisco"}}json_objectの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON {shipping} FROM po_ship;関連項目:
-
select_list構文の詳細は、Oracle Database SQL言語リファレンスを参照してください -
SQL/JSONファンクション
json_objectおよび同等のJSONコンストラクタ{...}構文の詳細は、『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンスに関する項を参照してください -
SQL識別子の構文については、『Oracle Database SQL言語リファレンス』のOracle Database SQL言語リファレンスに関する項を参照してください
親トピック: SQLを使用したJSONデータの生成
24.4 SQL/JSONファンクションJSON_ARRAY
SQL/JSONファンクションjson_arrayは、引数のSQL式を評価した結果からJSON配列を構成します。
最も単純なケースでは、json_arrayに指定する評価済引数は、JSON配列要素としてJSON値が生成されるSQL値です。結果の配列には、指定する引数ごとに要素が含められます(引数式がSQL NULLに評価される場合と、ABSENT ON NULL句が適用される場合を除く)。配列要素の順序は、引数の順序と同じです。
json_arrayの引数として使用できるSQL値には、SQLスカラー、コレクション・インスタンス、ユーザー定義のオブジェクト型インスタンスなど複数の種類があります。また、引数には(副)問合せ式を指定できます。この場合、配列要素は問合せによって返される値が順番に(ORDER BYが存在する場合はそれに従って)設定されます。
例24-7 値引数の指定があるJSON_ARRAYを使用したJSON配列の構成
この例では、データベース表hr.jobs (標準のデータベース・スキーマHRから)に含まれる各従業員の仕事のJSONオブジェクトが構成されます。オブジェクトのフィールドは、役職と給与の範囲です。給与の範囲(フィールドsalaryRange)は、仕事に対する最小給与と最大給与の2つの数値の配列です。これらの値は、SQL列min_salaryおよびmax_salaryから取得されます。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_object('title' VALUE job_title,
'salaryRange' VALUE json_array(min_salary, max_salary)
RETURNING JSON)
FROM jobs;
JSON_OBJECT('TITLE'ISJOB_TITLE,'SALARYRANGE'ISJSON_ARRAY(MIN_SALARY,
--------------------------------------------------------------------
{"title":"President","salaryRange":[20080,40000]}
{"title":"Administration Vice President","salaryRange":[15000,30000]}
{"title":"Administration Assistant","salaryRange":[3000,6000]}
{"title":"Finance Manager","salaryRange":[8200,16000]}
{"title":"Accountant","salaryRange":[4200,9000]}
{"title":"Accounting Manager","salaryRange":[8200,16000]}
{"title":"Public Accountant","salaryRange":[4200,9000]}
{"title":"Sales Manager","salaryRange":[10000,20080]}
{"title":"Sales Representative","salaryRange":[6000,12008]}
{"title":"Purchasing Manager","salaryRange":[8000,15000]}
{"title":"Purchasing Clerk","salaryRange":[2500,5500]}
{"title":"Stock Manager","salaryRange":[5500,8500]}
{"title":"Stock Clerk","salaryRange":[2008,5000]}
{"title":"Shipping Clerk","salaryRange":[2500,5500]}
{"title":"Programmer","salaryRange":[4000,10000]}
{"title":"Marketing Manager","salaryRange":[9000,15000]}
{"title":"Marketing Representative","salaryRange":[4000,9000]}
{"title":"Human Resources Representative","salaryRange":[4000,9000]}
{"title":"Public Relations Representative","salaryRange":[4500,10500]}
JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON { 'title' VALUE job_title,
'salaryRange' VALUE [ min_salary, max_salary ] }
FROM jobs;
例24-8 問合せ引数の指定があるJSON_ARRAYを使用したJSON配列の構成
この問合せは、json_arrayファンクションに副問合せを引数として渡します。副問合せによってjson_objectファンクションが起動され、オブジェクト値が配列要素として生成されます。配列要素は、副問合せの値を列salaryでソートするキーワードORDER BYによって、フィールドsalの値でソートされます。
ここではRETURNING JSONを使用することで、デフォルトの戻り型VARCHAR2(4000)ではなく、JSONデータがJSONデータ型として返されるように指定しています。
SELECT json_array(SELECT json_object('id' : employee_id,
'name' : last_name,
'sal' : salary)
RETURNING JSON
FROM employees
WHERE salary > 12000
ORDER BY salary) by_salary;
JSONデータの戻り型はJSONであるため、これは同じ問合せの代替の構文です。
SELECT JSON [ SELECT JSON {'id' : employee_id,
'name' : last_name,
'sal' : salary}
FROM employees
WHERE salary > 12000
ORDER BY salary ] by_salary;関連項目:
SQL/JSONファンクションjson_arrayおよび同等のJSONコンストラクタ([…])の構文の詳細は、Oracle Database SQL言語リファレンス
親トピック: SQLを使用したJSONデータの生成
24.5 SQL/JSONファンクションJSON_OBJECTAGG
SQL/JSONファンクションjson_objectaggは、グループ化されたSQL問合せの複数行の情報をオブジェクト・メンバーとして集計して、JSONオブジェクトを構成します。
結果のオブジェクトに含まれるメンバーの数が引数の数を直接反映するSQL/JSONファンクションjson_objectの場合とは異なり、json_objectaggでは、結果のオブジェクトのサイズは現在の問合せ対象データを反映します。このように、問合せ対象のデータによって結果のサイズが変わる可能性があります。
例24-9 JSON_OBJECTAGGを使用したJSONオブジェクトの構成
この例では、表hr.departments(標準のデータベース・スキーマHR)から単一のJSONオブジェクトを構成します。その際、列department_nameから取得したフィールド名と列department_idから取得したフィールド値が使用されます。
SQL/JSON条件is jsonと同様に、キーワードSTRICTおよびWITH UNIQUE KEYSはファンクションjson_objectおよびjson_objectaggとともに使用できます。それぞれの動作はis jsonと同じです。
SELECT json_objectagg(department_name VALUE department_id)
FROM departments;
-- The returned object is pretty-printed here for clarity.
-- The order of the object members is arbitrary.
JSON_OBJECTAGG(DEPARTMENT_NAMEISDEPARTMENT_ID)
----------------------------------------------
{"Administration": 10,
"Marketing": 20,
"Purchasing": 30,
"Human Resources": 40,
"Shipping": 50,
"IT": 60,
"Public Relations": 70,
"Sales": 80,
"Executive": 90,
"Finance": 100,
"Accounting": 110,
"Treasury": 120,
"Corporate Tax": 130,
"Control And Credit": 140,
"Shareholder Services": 150,
"Benefits": 160,
"Manufacturing": 170,
"Construction": 180,
"Contracting": 190,
"Operations": 200,
"IT Support": 210,
"NOC": 220,
"IT Helpdesk": 230,
"Government Sales": 240,
"Retail Sales": 250,
"Recruiting": 260,
"Payroll": 270}
関連項目
関連項目:
SQL/JSONファンクションjson_objectaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。
親トピック: SQLを使用したJSONデータの生成
24.6 SQL/JSONファンクションJSON_ARRAYAGG
SQL/JSONファンクションjson_arrayaggは、グループ化されたSQL問合せの複数行の情報を配列要素として集計して、JSON配列を構成します。配列要素の順序は、デフォルトでは、問合せ結果の順序に反映されますが、ORDER BY句を使用すると、配列要素の順序を指定できます。
結果の配列に含まれる要素の数が引数の数を直接反映するSQL/JSONファンクションjson_arrayの場合とは異なり、json_arrayaggでは、結果の配列のサイズは現在の問合せ対象データを反映します。このように、問合せ対象のデータによって結果のサイズが変わる可能性があります。
例24-10 JSON_ARRAYAGGを使用したJSON配列の構成
この例では、表hr.employees(標準のデータベース・スキーマHRから)の従業員のうち、6名以上の従業員を管理するマネージャである従業員それぞれのJSONオブジェクトが構成されます。オブジェクトには、マネージャID番号、マネージャ名、そのマネージャに報告する従業員の数、それらの従業員のID番号用のフィールドがあります。
配列内に含まれる従業員のID番号の順序は、json_arrayaggのORDER BY句で決定されます。ORDER BYのデフォルトの方向はASC (昇順)です。配列要素(数値)は、数値順の昇順に並べられます。
SELECT json_object('id' VALUE mgr.employee_id,
'manager' VALUE (mgr.first_name || ' '|| mgr.last_name),
'numReports' VALUE count(rpt.employee_id),
'reports' VALUE json_arrayagg(rpt.employee_id
ORDER BY rpt.employee_id))
FROM employees mgr, employees rpt
WHERE mgr.employee_id = rpt.manager_id
GROUP BY mgr.employee_id, mgr.last_name, mgr.first_name
HAVING count(rpt.employee_id) > 6;
-- The returned object is pretty-printed here for clarity.
JSON_OBJECT('ID'ISMGR.EMPLOYEE_ID,'MANAGER'VALUE(MGR.FIRST_NAME||''||MGR.LAST_NAME)
-----------------------------------------------------------------------------------
{"id": 100,
"manager": "Steven King",
"numReports": 14,
"reports": [101,102,114,120,121,122,123,124,145,146,147,148,149,201]}
{"id": 120,
"manager": "Matthew Weiss",
"numReports": 8,
"reports": [125,126,127,128,180,181,182,183]}
{"id": 121,
"manager": "Adam Fripp",
"numReports": 8,
"reports": [129,130,131,132,184,185,186,187]}
{"id": 122,
"manager": "Payam Kaufling",
"numReports": 8,
"reports": [133,134,135,136,188,189,190,191]}
{"id": 123,
"manager": "Shanta Vollman",
"numReports": 8,
"reports": [137,138,139,140,192,193,194,195]}
{"id": 124,
"manager": "Kevin Mourgos",
"numReports": 8,
"reports": [141,142,143,144,196,197,198,199]}
例24-11 SQL副問合せを使用したネストされた配列を持つJSONオブジェクトの生成
この例は、2つの表: countriesとregionsの間のSQL左外部結合を示しています。表countriesには外部キーregion_idがあり、これは表regionsの主キーと結合され、region_idという名前が付けられます。
問合せは、表regionsの各行に対してJSONオブジェクトを返します。これらのリージョンの各オブジェクトには、値が国オブジェクトの配列(そのリージョン内の国)であるcountriesフィールドがあります。
SELECT json_object(
'region' : region_name,
'countries' :
(SELECT json_arrayagg(json_object('id' : country_id,
'name' : country_name))
FROM countries c
WHERE c.region_id = r.region_id))
FROM regions r;
問合せは、次のようなオブジェクトになります。
{"region" : "Europe",
"countries" : [ {"id" : "BE",
"name" : "Belgium"},
{"id" : "CH",
"name" : "Switzerland"},
{"id" : "DE",
"name" : "Germany"},
{"id" : "DK",
"name" : "Denmark"},
{"id" : "FR",
"name" : "France"},
{"id" : "IT",
"name" : "Italy"},
{"id" : "NL",
"name" : "Netherlands"},
{"id" : "UK",
"name" : "United Kingdom"} ]}関連項目
関連項目:
SQL/JSONファンクションjson_arrayaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。
親トピック: SQLを使用したJSONデータの生成
24.7 JSON生成に基づく読取り専用ビュー
JSON生成関数またはコンストラクタJSONを使用して読取り専用ビューを作成できます。アクセス権があるユーザーは誰でも、そのビューを読取り専用表であるかのように使用できます。JSONデータのユーザーは、それがそれ自体として格納されているかや必要に応じて生成されているかを把握する必要も気にする必要もありません。
例24-12に、これを示します。結果となるビューは、列ID (部門識別番号)およびDATA (部門の従業員を含む、その部門のJSONデータ)がある読取り専用表であるかのように使用できます。SQL*Plusコマンドdescribeを使用すると、次のようになります:
describe department_view;
Name Null? Type
---------------------------
ID NOT NULL NUMBER(4)
DATA JSON特別なJSONコンストラクタ構文が使用されているため(JSON {...})、列DATAのデータ型はJSONです。基にある格納データは、HRのサンプルスキーマHR、表DEPARTMENT、LOCATION、EMPLOYEESおよびJOBSから取得されます。
このビューの各行に、単一の部門についての情報(ID、名前、住所および従業員)が表示されます。部門の住所はJSONオブジェクトです。
部門の従業員のデータは従業員オブジェクトの配列であり、各オブジェクトにその従業員のID、氏名および役職が含まれています。その名前は、名と姓が別々に格納される列データで構成されます。
このビューを問い合せると、JSON生成関数を呼び出すSQLコードが評価されます。例24-13に、部門90について単一の文書を返す問合せを示します。
読取りのパフォーマンスを向上させるには、ビューをマテリアライズします。
JSON生成ビューは読取り専用であるため、更新できません(INSTEAD OFトリガー脚注2を使用する場合を除く)。JSONデータが提供される更新可能なビューが必要な場合は、JSONリレーショナル二面性ビューを作成できます。
例24-12 JSON生成を使用するビューの作成
CREATE VIEW department_view AS
SELECT dep.department_id id,
JSON {'id' : dep.department_id,
'departmentName' : dep.department_name,
'departmentAddress' : JSON {'street' : loc.street_address,
'zip' : loc.postal_code,
'city' : loc.city,
'state' : loc.state_province,
'country' : loc.country_id},
'employees' : [ SELECT JSON {'id' : emp.employee_id,
'name' : emp.first_name || ' ' || emp.last_name,
'title' : (SELECT job_title
FROM jobs job
WHERE job.job_id = emp.job_id)}
FROM employees emp
WHERE emp.department_id = dep.department_id ]} data
FROM departments dep, locations loc
WHERE dep.location_id = loc.location_id;
例24-13 DEPARTMENT_VIEWから生成されたJSON文書
この例では、部門90について生成されるJSON文書をフォーマット出力します。例24-12のCREATE VIEW文で使用されている副問合せに対応する、埋込みオブジェクト(フィールドdepartmentAddress、および配列employeesの要素)に注目してください。
SELECT json_serialize(data pretty) FROM department_view WHERE id = 90;
{"id" : 90,
"departmentName" : "Executive",
"departmentAddress" : {"street" : "2004 Charade Rd",
"zip" : "98199",
"city" : "Seattle",
"state" : "Washington",
"country" : "US"},
"employees" : [ {"id" : 100,
"name" : "Steven King",
"title" : "President"},
{"id" : 101,
"name" : "Neena Kochhar",
"title" : "Administration Vice President"},
{"id" : 102,
"name" : "Lex De Haan",
"title" : "Administration Vice President"} ]}関連項目:
-
サンプル・スキーマ
HRについては、『Oracle Databaseサンプル・スキーマ』のHRサンプル・スキーマ表の説明、およびhttps://github.com/oracle-samples/db-sample-schemasを参照してください -
JSONデータの更新可能ビューについては、『JSONリレーショナル二面性開発者ガイド』の更新可能なJSONリレーショナル二面性ビューを参照してください
-
マテリアライズド・ビューの作成と使用については、『Oracle Databaseデータ・ウェアハウス・ガイド』の基本マテリアライズド・ビューを参照してください。
親トピック: SQLを使用したJSONデータの生成
脚注の凡例
脚注1: JSONデータに関するSQL/JSON生成関数の動作は、XMLデータに関するSQL/XML生成関数の動作と似ています。脚注2:
INSTEAD OFトリガーについては、『Oracle Database PL/SQL言語リファレンス』のINSTEAD OF DMLトリガーを参照してください