SQL/JSONファンクションを使用したJSONデータの生成

23.1 SQL/JSONの生成関数の概要

SQL/JSONファンクションjson_object、json_array、json_objectaggおよびjson_arrayaggを使用して、データベース内の非JSONデータからJSONデータを構成できます。JSONデータは、SQL値として戻されます。

これらの生成関数を使用すると、SQL問合せからJSONデータを直接簡単に構成できるようになります。非JSONデータをJSONオブジェクトおよびJSON配列として表すことが可能になります。これらの関数のコールをネストすることで、複雑で構造的なJSON文書を生成できます。ネストされた副問合せは、1対多関係を表すJSONコレクションを生成できます。^Foot 1

非JSONデータからJSONデータを構成するための最適な方法

SQL/JSON生成関数の使用のかわりとなる方法は、エラーが発生しやすかったり、非効率的であることが一般的です。

文字列の連結を使用したJSON文書の生成では、エラーが発生しやすくなります。特に、二重引用符(")などの特殊文字をいつ、どのようにエスケープするかという点について、順守する必要がある複雑なルールが多数あります。これらのルールは、見逃されたり誤って解釈されることが多く、その結果、正しくないJSONデータが生成される可能性が生じます。
非JSONの結果セットをデータベースから読み取り、クライアント側のアプリケーション・コードを使用してJSONデータを生成する方法は、特にネットワーク・オーバーヘッドが原因で非常に効率が悪くなることが一般的です。1対多関係をJSONデータとして表す場合は、必要な非JSONデータをすべて収集するために、複数のSELECT操作が必要になることが多くなります。生成する文書で複数のレベルの1対多関係が示される場合は、この方法では必要なコストが増えることが考えられます。

SQL/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値として戻されます。オプションのRETURNING句を使用して、VARCHAR2サイズを指定するか、かわりにCLOBまたはBLOBの戻り値を指定できます。BLOBが戻り型の場合、文字セットはAL32UTF8です。

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

SQL/JSON生成関数は入力としてSQLの値を受け取り、戻されるSQL値内のJSON値としてこれらをレンダリングします。入力値をJSONとして表示する方法は、SQLデータ型によって異なります。

SQL/JSON生成関数のオプションの動作

オプションとして、SQL NULLを処理する句、RETURNING句およびキーワードSTRICTを指定できます。

NULLを処理する句—入力評価の結果のSQL NULL値をどのように扱うかを決定します。
- NULL ON NULL—入力のSQL NULL値が、出力のJSON nullに変換されます。これは、json_objectとjson_objectaggのデフォルト動作です。
- ABSENT ON NULL—入力のSQL NULL値の結果、対応する出力がない状態になります。これは、json_arrayとjson_arrayaggのデフォルト動作です。
RETURNING句—関数の戻り値に使用されるSQLデータ型です。デフォルトはVARCHAR2(4000)です。
STRICTキーワード—指定すると、戻されるJSONデータが整形式になっているかチェックされます。STRICTを指定して戻されたデータが整形式でない場合には、エラーが発生します。

関連項目

関連項目:

SQL/JSONファンクションjson_arrayの詳細は、Oracle Database SQL言語リファレンスを参照してください。
SQL/JSONファンクションjson_arrayaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。
SQL/JSONファンクションjson_objectの詳細は、Oracle Database SQL言語リファレンスを参照してください。
SQL/JSONファンクションjson_objectaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。

親トピック: SQL/JSONファンクションを使用したJSONデータの生成

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

SQL/JSON生成関数は入力としてSQLの値を受け取り、戻されるSQL値内のJSON値としてこれらをレンダリングします。入力値をJSONとして表示する方法は、SQLデータ型によって異なります。

デフォルトでは、SQL NUMBER値はJSONの数値として出力に表示され、SQL VARCHAR2値はJSON文字列として表示されます。たとえば、デフォルトではVARCHAR2値'{}'はJSON文字列"{}"として表示されます。

実際に入力値がすでにJSONデータを表していることを認識しているか想定していて、値がそのまま維持されるように、これを生成関数に渡す必要がある場合があります。たとえば、入力が'{}'の場合、JSON文字列の"{}"ではなく、空のJSONオブジェクトの{}として、解釈(表示)させる必要がある場合があります。

入力式の後にキーワードFORMAT JSONを追加してこれを実行すると、そこから生成される値がすでにJSONデータを表しているとみなされることを宣言できます。同様に、キーワードAS JSONとともにSQLファンクションtreatを生成関数の入力値に適用できます。結果は、FORMAT JSONを使用した場合と同じです。

多くの場合、実際に入力値がJSONデータであることは自動的に認識できますが、その場合、入力はその後に明示的なFORMAT JSON宣言が続くかのように扱われます。これは、たとえば、値の式がSQL/JSON生成関数またはファンクションjson_queryを呼び出した場合に当てはまります。

FORMAT JSONを特定の入力値に指定しない場合で、Oracleでも値がJSONデータであることを識別できない場合には、この値は通常の(非JSONの)SQLデータであるとみなされます。この場合は、次のようにシリアライズされます(その他のSQL値ではエラーが発生します)。

ユーザー定義のSQLオブジェクト型のインスタンスは、フィールド名がオブジェクト属性名から取得され、フィールド値が(JSON生成が再帰的に適用される)オブジェクト属性値から取得されるJSONオブジェクトとしてレンダリングされます。
SQLコレクション型のインスタンスは、(JSON生成が再帰的に適用される)コレクション要素の値から要素の値が取得されるJSON配列としてレンダリングされます。
VARCHAR2、CLOBまたはNVARCHAR値は、二重引用符(")で囲まれ、JSON標準に準拠するように、必要に応じて文字がエスケープされます。
数値(NUMBER、BINARY_DOUBLEまたはBINARY_FLOAT)は、JSONの数値として表示されます。(引用符では囲まれません。)

正の無限大および負の無限大の数値、および数値演算の未定義の結果である値(非数値、つまり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形式で表示され、その結果は二重引用符(")で囲まれます。
BOOLEAN PL/SQL値はJSONのtrueまたはfalseとして表示されます。(引用符では囲まれません。)
NULL値は、NULLデータ型に関係なく、JSONのnullとして表示されます。(引用符では囲まれません。)

ノート:

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

入力引数の形式は、関数から返されるデータの形式に影響する可能性があります。特に、入力がJSONの形式であると宣言されるか、自動的に判断された場合は、戻り値の計算時にJSONデータとして扱われます。例23-1に、これを示します。ここでは、FORMAT JSONを明示的に使用して、SQL文字列"true"をJSONのブール値trueに解釈しています。

例23-1 入力値のJSONとしての宣言

この例では、JSONのブール値trueおよびfalseが使用されるようにするために、FORMAT JSONをSQL文字列値'true'および'false'に対して指定します。FORMAT JSONを指定しないと、フィールドhasCommissionの値はJSON文字列値"true"および"false"となり、JSONブール値trueおよびfalseにはなりません。

SELECT json_object('name'          VALUE first_name || ' ' || last_name,
                   'hasCommission' VALUE
                     CASE WHEN commission_pct IS NULL THEN 'false' ELSE 'true'
                     END FORMAT JSON)
  FROM employees WHERE first_name LIKE 'W%';

JSON_OBJECT('NAME'ISFIRST_NAME||''||LAST_NAME,'
-----------------------------------------------
{"name":"William Gietz","hasCommission":false}
{"name":"William Smith","hasCommission":true}
{"name":"Winston Taylor","hasCommission":false}

関連項目

親トピック: SQL/JSONファンクションを使用したJSONデータの生成

23.3 SQL/JSONファンクションJSON_OBJECT

SQL/JSONファンクションjson_objectは、引数のSQL式を評価した結果からJSONオブジェクトを構成します。

任意の数の引数を指定でき、各引数は次のいずれかです。

明示的なフィールド名/値ペア。例: answer : 42。

名前/値ペア引数は、生成されたJSONオブジェクトのオブジェクト・メンバーを指定します(値式がSQL NULLに評価される場合、およびABSENT ON NULL句が適用される場合を除く)。名前と値はSQL式です。名前式は、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と同じです。

例23-2 JSON_OBJECTでの名前/値ペアの使用

この例では、表hr.employees (標準のデータベース・スキーマHRから)の従業員のうち、給与が15000を超える従業員それぞれのJSONオブジェクトが構成されます。

明示的な名前/値のペアを渡してJSONオブジェクトのメンバーを指定します。このオブジェクトには、フィールドcontactInfoの値として、フィールドmailとphoneのオブジェクトが含められています。

SELECT json_object('id'          : employee_id,
                   'name'        : first_name || ' ' || last_name,

                   'contactInfo' : json_object('mail'  : email,
                                               'phone' : phone_number),
                   'hireDate'    : hire_date,
                   'pay'         : salary)
  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,

例23-3 JSON_OBJECTでの列名の使用

この例では、employee_idが101である従業員のJSONオブジェクトを構成します。生成されるフィールドには列の名前が付けられ、大/小文字が区別されます。

SELECT json_object(last_name,
                   'contactInfo' : json_object(email, phone_number),
                   hire_date,
                   salary) 
  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}

例23-4 JSON_OBJECTでのワイルドカード(*)の使用

この例では、給与が15000を超える従業員ごとにJSONオブジェクトを構成します。従業員表の各列は、そのフィールド名が(大文字)列名である1つのオブジェクト・メンバーの構成に使用されます。SQL NULL値によってJSONフィールド値がnullになることに注意してください。

SELECT json_object(*)
  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}

例23-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'     VALUE 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"}

例23-6 JSON_OBJECTでのユーザー定義のオブジェクト型インスタンスの使用

この例では、オブジェクト型がshipping_tの列shippingを使用して表po_shipを作成します。(SQL/JSONファンクションjson_valueを使用してJSONデータからshipping_tインスタンスを構成します。例18-3を参照。)

次に、json_objectを使用して、列po_ship.shippingのSQLオブジェクト型インスタンスから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) 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"}}

関連項目

関連項目:

select_list構文の詳細は、Oracle Database SQL言語リファレンスを参照してください
SQL/JSONファンクションjson_objectの詳細は、Oracle Database SQL言語リファレンスを参照してください。
SQL識別子の構文は、Oracle Database SQL言語リファレンスを参照してください。

親トピック: SQL/JSONファンクションを使用したJSONデータの生成

23.4 SQL/JSONファンクションJSON_ARRAY

SQL/JSONファンクションjson_arrayは、引数のSQL式を評価した結果からJSON配列を構成します。

最も単純なケースでは、json_arrayに指定する評価済引数は、JSON配列要素としてJSON値に変換されるSQL値です。結果の配列には、指定する引数ごとに要素が含められます(引数式がSQL NULLに評価される場合と、ABSENT ON NULL句が適用される場合を除く)。配列要素の順序は、引数の順序と同じです。

json_arrayの引数として使用できるSQL値には、スカラー、コレクション・インスタンス、ユーザー定義のオブジェクト型インスタンスなど、いくつかの種類があります。

例23-7 JSON_ARRAYを使用したJSON配列の構成

この例では、データベース表hr.jobs (標準のデータベース・スキーマHRから)に含まれる各従業員の仕事のJSONオブジェクトが構成されます。オブジェクトのフィールドは、役職と給与の範囲です。給与の範囲(フィールドsalaryRange)は、仕事に対する最小給与と最大給与の2つの数値の配列です。これらの値は、SQL列min_salaryおよびmax_salaryから取得されます。

SELECT json_object('title'       VALUE job_title, 
                   'salaryRange' VALUE json_array(min_salary, max_salary))
  FROM jobs;

JSON_OBJECT('TITLE'ISJOB_TITLE,'SALARYRANGE'ISJSON_ARRAY(MIN_SALARY,MAX_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]}

関連項目

関連項目:

SQL/JSONファンクションjson_arrayの詳細は、Oracle Database SQL言語リファレンスを参照してください。

親トピック: SQL/JSONファンクションを使用したJSONデータの生成

23.5 SQL/JSONファンクションJSON_OBJECTAGG

SQL/JSONファンクションjson_objectaggは、グループ化されたSQL問合せの複数行の情報をオブジェクト・メンバーとして集計して、JSONオブジェクトを構成します。

結果のオブジェクトに含まれるメンバーの数が引数の数を直接反映するSQL/JSONファンクションjson_objectの場合とは異なり、json_objectaggでは、結果のオブジェクトのサイズは現在の問合せ対象データを反映します。このように、問合せ対象のデータによって結果のサイズが変わる可能性があります。

例23-8 JSON_OBJECTAGGを使用したJSONオブジェクトの構成

この例では、表hr.departments(標準のデータベース・スキーマHR)から単一のJSONオブジェクトを構成します。その際、列department_nameから取得したフィールド名と列department_idから取得したフィールド値が使用されます。

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の生成関数の概要

関連項目:

SQL/JSONファンクションjson_objectaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。

親トピック: SQL/JSONファンクションを使用したJSONデータの生成

23.6 SQL/JSONファンクションJSON_ARRAYAGG

SQL/JSONファンクションjson_arrayaggは、グループ化されたSQL問合せの複数行の情報を配列要素として集計して、JSON配列を構成します。配列要素の順序は、デフォルトでは、問合せ結果の順序に反映されますが、ORDER BY句を使用すると、配列要素の順序を指定できます。

結果の配列に含まれる要素の数が引数の数を直接反映するSQL/JSONファンクションjson_arrayの場合とは異なり、json_arrayaggでは、結果の配列のサイズは現在の問合せ対象データを反映します。このように、問合せ対象のデータによって結果のサイズが変わる可能性があります。

例23-9 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]}

例23-10 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の生成関数の概要

関連項目:

SQL/JSONファンクションjson_arrayaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。

親トピック: SQL/JSONファンクションを使用したJSONデータの生成