JSON_ARRAYAGG (SQL)

構文

JSON_ARRAYAGG([ ALL | DISTINCT [BY(col-list)] ] 
  string-expr 
  [ %FOREACH(col-list) ] [ %AFTERHAVING ])

概要

JSON_ARRAYAGG 集約関数は、指定された列の値の JSON フォーマットの配列を返します。JSON 配列のフォーマットの詳細は、JSON_ARRAY 関数を参照してください。

シンプルな JSON_ARRAYAGG (または JSON_ARRAYAGG ALL) は、選択された行の string-expr に対応するすべての値を含む JSON 配列を返します。配列では、string-expr が空の文字列 ('') の行を ("\u0000") によって表します。string-expr が NULL の行は、配列に含まれません。string-expr の値が 1 つのみでそれが空の文字列 ('') である場合、JSON_ARRAYAGG は JOSN 配列 ["\u0000"] を返します。string-expr の値がすべて NULL である場合、JSON_ARRAYAGG は空の JSON 配列 [] を返します。

JSON_ARRAYAGG DISTINCT は、選択された行の string-expr に対応するすべての異なる (一意の) 値で構成される JSON 配列を返します (JSON_ARRAYAGG(DISTINCT col1))。NULL の string-expr は、JSON 配列に含まれません。JSON_ARRAYAGG(DISTINCT BY(col2) col1) は、col2 値が個別 (一意) であるレコードのこれらの col1 フィールド値のみが含まれる JSON 配列を返します。ただし、個別の col2 値には、個別値として NULL が 1 つ含まれる場合があります。

JSON_ARRAYAGG string-expr にストリーム・フィールドを指定することはできません。ストリーム・フィールドを指定すると、SQLCODE -37 になります。

例

この例では、Sample.Person テーブルの Home_State 列にある、先頭の文字が "A" であるすべての値の JSON 配列を返します。

  SELECT JSON_ARRAYAGG(Home_State)
  FROM Sample.Person
  WHERE Home_State %STARTSWITH 'A'

この JSON 配列には重複値が含まれます。

以下の例では、Sample.Person テーブルの Home_State 列から先頭の文字が “A” であるすべての個別値 (一意の値) を抽出し、それを JSON 配列を含むホスト変数に格納し、返します。

  SELECT DISTINCT JSON_ARRAYAGG(Home_State) AS DistinctStates
  FROM Sample.Person
  WHERE Home_State %STARTSWITH 'A'

以下の例では、各州の Home_City 列にあるすべての値の JSON 配列を作成し、これらの都市の値の数を州ごとにカウントします。それぞれの Home_State の行には、その州のすべての Home_City 値の JSON 配列が格納されます。これらの JSON 配列では、都市名が重複している可能性があります。

SELECT Home_State,
       COUNT(Home_City) AS CityCount,
       JSON_ARRAYAGG(Home_City) AS ArrayAllCities 
FROM Sample.Person
GROUP BY Home_State

以下の例に示すように、各州の Home_City 列にあるすべての個別値の JSON 配列を生成した方が役に立つことが普通です。

SELECT DISTINCT Home_State,
       COUNT(DISTINCT Home_City) AS DistCityCount,
       COUNT(Home_City) AS TotCityCount,
       JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities 
FROM Sample.Person GROUP BY Home_State

この例は、各州の個別の都市名をカウントした整数と、都市名の合計をカウントした整数の両方を返します。

以下のダイナミック SQL の例では、%SelectMode プロパティを使用して、DOB 日付フィールドによって返される値の JSON 配列に ODBC 表示モードを指定しています。

  SET myquery = 2
  SET myquery(1) = "SELECT JSON_ARRAYAGG(DOB) AS DOBs "
  SET myquery(2) = "FROM Sample.Person WHERE Name %STARTSWITH 'A'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(.myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"

以下の例では、%FOREACH キーワードを使用します。これは、Home_State の age 値の JSON 配列を含むそれぞれ個別の Home_State の行を返します。

SELECT DISTINCT Home_State,
    JSON_ARRAYAGG(Age %FOREACH(Home_State))
FROM Sample.Person
WHERE Home_State %STARTSWITH 'M'

以下の例では、%AFTERHAVING キーワードを使用します。ここでは、HAVING 節の条件 (先頭の文字が “M” である名前) を満たす Name 値が 1 つ以上ある Home_State 行をすべて返します。最初の JSON_ARRAYAGG 関数は、該当の州についてすべての名前の JSON 配列を返します。2 番目の JSON_ARRAYAGG 関数は、HAVING 節の条件を満たす名前のみを含む JSON 配列を返します。

SELECT Home_State,
       JSON_ARRAYAGG(Name) AS AllNames,
       JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames 
FROM Sample.Person GROUP BY Home_State
HAVING Name LIKE 'M%' ORDER BY Home_State

関連項目

• 集約関数の概要

• JSON_ARRAY 関数

• IS JSON の述語条件

• LIST 集約関数

• %DLIST 集約関数

• XMLAGG 集約関数

• SELECT 文