SQL/JSONファンクションJSON

25.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が存在する場合はそれに従って)設定されます。

例25-8 値引数の指定がある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;

例25-9 問合せ引数の指定があるJSON_ARRAYを使用したJSON配列の構成

この問合せは、json_arrayファンクションに副問合せを引数として渡します。副問合せによってjson_objectファンクションが起動され、オブジェクト値が配列要素として生成されます。配列要素は、副問合せの値を列salaryでソートするキーワードORDER BYによって、フィールドsalの値でソートされます。

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;