SQL/JSONファンクションJSON

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

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

ノート:

json_arrayaggで使用されるORDER BY句は、列(またはその別名)を参照する必要があります。かわりに位置指定のORDER BY句を使用すると、エラーが発生します:

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

例25-11 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]}

例25-12 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"} ]}