Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

For information on migrating to InterSystems IRISOpens in a new tab, see Why Migrate to InterSystems IRIS?

JSON_ARRAYAGG

JSON フォーマットの配列の値を作成する集約関数です。

Synopsis

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

引数

ALL オプションJSON_ARRAYAGG が JSON 配列 (string-expr のすべての値が含まれます) を返すように指定します。キーワードが指定されていない場合は、これが既定になります。
DISTINCT オプション — 一意の string-expr 値のみを含む JSON 配列を JSON_ARRAYAGG が返すように指定する DISTINCT 節。DISTINCT で BY(col-list) 従属節を指定できます。col-list には 1 つのフィールド、またはコンマ区切りのフィールドのリストを指定できます。
string-expr 文字列として評価する SQL 式。通常は選択されたテーブルの列の名前です。
%FOREACH(col-list) オプション — 列名、またはコンマで区切られた列名のリスト。%FOREACH の詳細は、"SELECT" を参照してください。
%AFTERHAVING オプションHAVING 節にある条件を適用します。

概要

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 になります。

エスケープ文字を格納するデータ値

  • 二重引用符:string-expr の値に二重引用符文字 (") が含まれている場合、JSON_ARRAYAGG はリテラルのエスケープ・シーケンス \" を使用してこの文字を表します。

  • バックスラッシュ:string-expr の値にバックスラッシュ文字 (\) が含まれている場合、JSON_ARRAYAGG はリテラルのエスケープ・シーケンス \\ を使用してこの文字を表します。

  • 一重引用符:string-expr の値にリテラル文字として一重引用符が含まれている場合、Caché SQL では、2 つの一重引用符 ('') としてこれを二重にしてこの文字をエスケープする必要があります。JSON_ARRAYAGG は一重引用符文字 ' としてこの文字を表します。

最大 JSON 配列サイズ

JSON_ARRAYAGG の既定の返りタイプは VARCHAR(8192) です。この長さには、JSON 配列フォーマット文字およびフィールド・データ文字が含まれます。返される値が 8192 よりも長い必要があると予想される場合、CAST 関数を使用してより長い返り値を指定できます。例えば、CAST(JSON_ARRAYAGG(value)) AS VARCHAR(12000)) と指定します。返された実際の JSON 配列が JSON_ARRAYAGG の返りタイプの長さよりも長い場合、Caché は、エラーを発行せずに返りタイプの長さで JSON 配列を切り捨てます。JSON 配列を切り捨てると閉じ括弧文字 ] が削除されるため、返り値が無効になります。

JSON_ARRAYAGG と %SelectMode

%SelectMode プロパティを使用して、JSON 配列の要素のデータ表示値を 0 = 論理 (既定)、1 = ODBC、2 = 表示のいずれかに指定できます。string-expr に %List 構造が含まれている場合、その要素は ODBC モードではコンマで区切られて表され、論理モードおよび表示モードでは %List フォーマット文字が \ エスケープ・シーケンスで表されます。これらの JSON \ エスケープ・シーケンスの一覧表は、"$ZCONVERT" で “エンコード変換” を参照してください。

JSON_ARRAYAGG と ORDER BY

JSON_ARRAYAGG 関数は、複数行のテーブル列の値を要素値の JSON 配列に結合します。すべての集約フィールドが評価された後に ORDER BY 節がクエリ結果セットに適用されるため、ORDER BY がリスト内の値の順序に直接影響することはありません。特定の状況下では、JSON_ARRAYAGG の結果が順番に表示されることがありますが、この順番を信頼すべきではありません。特定の集約結果値内にリストされる値を、明示的に順序付けすることはできません。

関連する集約関数

  • LIST は、コンマで区切られた値のリストを返します。

  • %DLIST は、各値の要素を含む Caché リストを返します。

  • XMLAGG は、値を連結した文字列を返します。

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

  &sql(SELECT JSON_ARRAYAGG(Home_State)
       INTO :statearray
       FROM Sample.Person
       WHERE Home_State %STARTSWITH 'A')
  WRITE "JSON array of states:",!,statearray

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

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

  ZNSPACE "SAMPLES"
  SET myquery = 2
  SET myquery(1) = "SELECT JSON_ARRAYAGG(DISTINCT Home_State) AS DistinctStates "
  SET myquery(2) = "FROM Sample.Person WHERE Home_State %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"

以下の SQL の例では、各州の 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

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

  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT Home_State,COUNT(DISTINCT Home_City) AS DistCityCount,"
  SET myquery(2) = "COUNT(Home_City) AS TotCityCount,"
  SET myquery(3) = "JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities "
  SET myquery(4) = "FROM Sample.Person GROUP BY Home_State"
  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"

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

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

  ZNSPACE "SAMPLES"
  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"

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

  ZNSPACE "SAMPLES"
  SET myquery = 3
  SET myquery(1) = "SELECT DISTINCT Home_State,"
  SET myquery(2) = "JSON_ARRAYAGG(Age %FOREACH(Home_State)) AgesForState "
  SET myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
  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"

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

  ZNSPACE "SAMPLES"
  SET myquery = 4
  SET myquery(1) = "SELECT Home_State,JSON_ARRAYAGG(Name) AS AllNames,"
  SET myquery(2) = "JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames "
  SET myquery(3) = "FROM Sample.Person GROUP BY Home_State "
  SET myquery(4) = "HAVING Name LIKE 'M%' ORDER BY Home_State"
  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"

関連項目

FeedbackOpens in a new tab