19 SQL/JSONファンクションを使用したJSONデータの生成
SQL/JSONファンクションのjson_object、json_array、json_objectaggおよびjson_arrayaggについて説明します。
トピック:
- SQL/JSONの生成関数の概要
SQL/JSONファンクションjson_object、json_array、json_objectaggおよびjson_arrayaggを使用して、データベース内の非JSONデータからJSONデータを構成できます。JSONデータは、SQL値として戻されます。 - JSON_OBJECT SQL/JSONファンクション
SQL/JSONファンクションjson_objectは、名前/値ペアからJSONオブジェクトを構成します。各ペアは、明示的な引数として提供されます。ペアの名前は、それぞれSQL識別子に評価される必要があります。各ペアの値には任意のSQL式を指定できます。名前と値は、キーワードVALUEで区切られます。 - JSON_ARRAY SQL/JSONファンクション
SQL/JSONファンクションjson_arrayは、引数のSQL式を評価した結果からJSON配列を構成します。各引数には、任意のSQL式を指定できます。配列要素の順序は、引数の順序と同じです。 - JSON_OBJECTAGG SQL/JSONファンクション
SQL/JSONファンクションjson_objectaggは、グループ化されたSQL問合せの複数行の情報をオブジェクト・メンバーとして集計して、JSONオブジェクトを構成します。 - JSON_ARRAYAGG SQL/JSONファンクション
SQL/JSONファンクションjson_arrayaggは、グループ化されたSQL問合せの複数行の情報を配列要素として集計して、JSON配列を構成します。配列要素の順序は、デフォルトでは、問合せ結果の順序に反映されますが、ORDER BY句を使用すると、配列要素の順序を指定できます。
19.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では、それぞれに引数としてSQLの名前/値ペアおよび値を指定して、JSONオブジェクトまたは配列を構成します。引数の数は、それぞれオブジェクト・メンバーと配列要素の数と対応します(引数の式がSQLNULLに評価される場合とABSENT ON NULL句が適用される場合は除く)。それぞれの名前に、SQL識別子の構文が必要です。各値は、任意のSQL値にすることができます。これには、多くても1つの項目(単一の列を持つ単一の行、このような問合せ引数が複数の行を戻すとエラーが発生する)を戻すスカラーSQL (副)問合せを使用して計算した値が含まれます。
-
関数
json_objectaggおよびjson_arrayaggは、集計のSQLファンクションです。これらの関数は、グループ化されたSQL問合せの行に格納された情報を、それぞれ、JSONオブジェクトと配列に変換します。引数の評価によって、オブジェクト・メンバーと配列要素の数が決まります。つまり、結果のサイズは現在の問合せ対象データを反映します。json_objectaggの場合、オブジェクト・メンバーの順序は指定されません。json_arrayaggの場合、配列要素の順序が問合せ結果の順序に反映されます。問合せでSQLORDER BYを使用すると、配列要素の順序を制御できます。
JSON_OBJECTおよびJSON_ARRAYの入力値の形式
関数json_arrayでは、引数としてサポートされている任意のデータ型のSQL値を使用できます。関数json_objectに渡す名前/値ペアの値引数に対しても同様です。一部のケースでは、そのような値が実際はJSONデータ(SQL文字列または数値として表される)であることを知っていたり、そのように予測されることがあります。入力値の式の後にキーワードFORMAT JSONを追加すると、この式の結果の値に対して期待される形式を宣言できます。
Oracleでその値が実際にはJSONデータであることが識別された場合は、そのデータの後に明示的にFORMAT JSONが宣言されたかのように扱われます。これは、たとえば、値の式がSQL/JSON生成関数の起動である場合に当てはまります。
FORMAT JSONを指定しない場合で、Oracleでも値がJSONデータであることを識別できない場合には、この値は通常の(非JSONの)SQLデータであると見なされます。この場合は、次のようにシリアライズされます(その他のSQL値ではエラーが発生します)。
-
VARCHAR2またはCLOBの値は、二重引用符(")で囲まれます。 -
数値はJSONの数値に変換されます。(引用符では囲まれません。)
-
DATEまたはTIMESTAMPの値はISO 8601形式に変換されます。その結果は、二重引用符(")で囲まれます。 -
BOOLEANPL/SQL値はJSONのtrueまたはfalseに変換されます。(引用符では囲まれません。) -
NULL値は、NULLデータ型に関係なく、JSONのnullに変換されます。
注意:
Oracle SQLでは空の文字列がNULLとして扱われるため、空のJSON文字列("")を構成する方法はありません。
入力引数の形式は、関数から返されるデータの形式に影響する可能性があります。特に、入力がJSONの形式であると判断された場合には、戻り値の計算時にJSONデータとして扱われます。例19-1でこれについて説明しています。ここでは、FORMAT JSONを明示的に使用して、SQL文字列"true"をJSONのブール値trueに解釈しています。
SQL/JSON生成関数のオプションの動作
オプションとして、SQL NULLを処理する句、RETURNING句およびキーワードSTRICTを指定できます。
-
NULLを処理する句: 入力評価の結果のSQLNULL値をどのように扱うかを決定します。-
NULL ON NULL: 入力のSQLNULL値が、出力のJSONnullに変換されます。これは、json_arrayとjson_arrayaggのデフォルト動作です。 -
ABSENT ON NULL: 入力のSQLNULL値の結果、対応する出力がない状態になります。これは、json_objectとjson_objectaggのデフォルト動作です。
-
-
RETURNING句: 関数の戻り値に使用されるSQLデータ型です。デフォルトはVARCHAR2(4000)です。 -
STRICTキーワード: 指定すると、戻されるJSONデータが整形式になっているかチェックされます。STRICTを指定して戻されたデータが整形式でない場合には、エラーが発生します。
SQL/JSON生成関数によって戻される結果
関数から戻されるJSONデータは、SQL VARCHAR2値として生成されます。このサイズは、オプションのRETURNING句で制御できます。集計のSQLファンクション(json_objectaggおよびjson_arrayagg)の場合は、RETURNING句のSQLデータ型としてCLOBも指定できます。
返されるデータ内のJSON値は、入力に含まれるSQL値から次のように導出されます。
-
SQLの数値は、JSONの数値に変換されます。
-
非
NULLおよび非数値のSQL値は、JSON文字列に変換されます。 -
SQL
NULL値は、オプションのNULLを処理する句で処理されます。
関連項目:
-
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言語リファレンスを参照してください。
例19-1 入力値のJSONとしての宣言
この例では、JSONのブール値trueおよびfalseが使用されるようにするために、FORMAT JSONをSQL文字列値'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}
19.2 JSON_OBJECT SQL/JSONファンクション
SQL/JSONファンクションjson_objectは、名前/値ペアからJSONオブジェクトを構成します。各ペアは、明示的な引数として提供されます。ペアの名前は、それぞれSQL識別子に評価される必要があります。各ペアの値には任意のSQL式を指定できます。名前と値は、キーワードVALUEで区切られます。
json_objectに指定する評価済引数は、明示的なオブジェクト・フィールド名とフィールド値です。結果のオブジェクトには、指定する名前/値ペアの引数ごとにメンバーが含められます(値式がSQL NULLに評価される場合と、ABSENT ON NULL句が適用される場合を除く)。
例19-2 JSON_OBJECTを使用したJSONオブジェクトの構成
この例では、表hr.employees(標準のデータベース・スキーマHRから)の従業員のうち、給与が15000より少ない従業員それぞれのJSONオブジェクトが構成されます。このオブジェクトには、フィールドcontactInfoの値として、フィールドmailとphoneのオブジェクトが含められています。
json_objectの戻り値はJSONデータになるため、フィールドcontactInfoの入力形式に対してFORMAT JSONが推定されていおり、ここで、明示的なFORMAT JSONは必要ありません。
SELECT json_object('id' VALUE employee_id,
'name' VALUE first_name || ' ' || last_name,
'hireDate' VALUE hire_date,
'pay' VALUE salary,
'contactInfo' VALUE json_object('mail' VALUE email,
'phone' VALUE phone_number)
FORMAT JSON)
FROM employees
WHERE salary > 15000;
-- The query returns rows such as this (pretty-printed here for clarity):
{"id":101,
"name":"Neena Kochhar",
"hireDate":"21-SEP-05",
"pay":17000,
"contactInfo":{"mail":"NKOCHHAR",
"phone":"515.123.4568"}}例19-3 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' VALUE state_province ABSENT ON NULL)
FROM 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"}
関連項目
関連項目:
-
SQL/JSONファンクション
json_objectの詳細は、Oracle Database SQL言語リファレンスを参照してください。 -
SQL識別子の構文は、Oracle Database SQL言語リファレンスを参照してください。
19.3 JSON_ARRAY SQL/JSONファンクション
SQL/JSONファンクションjson_arrayは、引数のSQL式を評価した結果からJSON配列を構成します。各引数には、任意のSQL式を指定できます。配列要素の順序は、引数の順序と同じです。
json_arrayに指定する評価済引数は、明示的な配列要素の値です。結果の配列には、指定する引数ごとに要素が含められます(引数式がSQL NULLに評価される場合と、ABSENT ON NULL句が適用される場合を除く)。
SQLの数値に評価される引数式は、JSONの数値に変換されます。非NULLおよび非数値の引数値は、JSON文字列に変換されます。
例19-4 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言語リファレンスを参照してください。
19.4 JSON_OBJECTAGG SQL/JSONファンクション
SQL/JSONファンクションjson_objectaggは、グループ化されたSQL問合せの複数行の情報をオブジェクト・メンバーとして集計して、JSONオブジェクトを構成します。
結果のオブジェクトに含まれるメンバーの数が引数の数を直接反映するSQL/JSONファンクションjson_objectの場合とは異なり、json_objectaggでは、結果のオブジェクトのサイズは現在の問合せ対象データを反映します。このように、問合せ対象のデータによって結果のサイズが変わる可能性があります。
例19-5 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ファンクションjson_objectaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。
19.5 JSON_ARRAYAGG SQL/JSONファンクション
SQL/JSONファンクションjson_arrayaggは、グループ化されたSQL問合せの複数行の情報を配列要素として集計して、JSON配列を構成します。配列要素の順序は、デフォルトでは、問合せ結果の順序に反映されますが、ORDER BY句を使用すると、配列要素の順序を指定できます。
結果の配列に含まれる要素の数が引数の数を直接反映するSQL/JSONファンクションjson_arrayの場合とは異なり、json_arrayaggでは、結果の配列のサイズは現在の問合せ対象データを反映します。このように、問合せ対象のデータによって結果のサイズが変わる可能性があります。
例19-6 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]}
関連項目
関連項目:
SQL/JSONファンクションjson_arrayaggの詳細は、Oracle Database SQL言語リファレンスを参照してください。
脚注の凡例
脚注1: JSONデータでのSQL/JSON生成関数の動作は、XMLデータのSQL/XML生成関数の動作と似ています。