第六章 SQL聚合函数 JSON_ARRAYAGG

创建JSON格式值数组的聚合函数。

注：IRIS可用，IRIS之前版本不可用。

大纲

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

参数

• ALL - 可选-指定JSON_ARRAYAGG返回一个JSON数组，其中包含string-expr的所有值。
  如果没有指定关键字，这是默认值。
• DISTINCT - 可选-一个DISTINCT子句，指定JSON_ARRAYAGG返回一个仅包含唯一字符串expr值的JSON数组。
  DISTINCT可以指定BY(colo -list)子句，其中colo -list可以是单个字段，也可以是用逗号分隔的字段列表。
• string-expr - 计算结果为字符串的SQL表达式。
  通常是所选表中列的名称。
• %FOREACH(col-list) - 可选-列名或以逗号分隔的列名列表。
• %AFTERHAVING - 可选-应用在HAVING子句中的条件。

描述

JSON_ARRAYAGG聚合函数返回指定列中值的JSON格式数组。

一个简单的JSON_ARRAYAGG(或JSON_ARRAYAGG ALL)返回一个JSON数组，其中包含所选行中string-expr的所有值。
字符串-expr为空字符串(")的行由数组中的(" u0000")表示。
字符串-expr为NULL的行不包含在数组中。
如果只有一个字符串-expr值，并且是空字符串(")，JSON_ARRAYAGG将返回JSON数组["\u0000"]。
如果所有的string-expr值为NULL, JSON_ARRAYAGG返回一个空的JSON数组[]。

JSON_ARRAYAGG DISTINCT返回一个JSON数组，由所选行中string-expr的所有不同(唯一)值组成:JSON_ARRAYAGG(DISTINCT col1)。
NULL字符串-expr不包含在JSON数组中。
JSON_ARRAYAGG(DISTINCT BY(col2) col1)返回一个JSON数组，该数组只包含记录中col2值是不同的(唯一的)的那些col1字段值。
但是请注意，不同的col2值可能包含一个单独的NULL值。

JSON_ARRAYAGG string-expr不能是流字段。
指定流字段的结果是SQLCODE -37。

包含转义字符的数据值

• 双引号:如果一个string-expr值包含一个双引号字符(")，JSON_ARRAYAGG使用字面转义序列\"来表示这个字符。
• 反斜杠：如果string-expr值包含反斜杠字符(\)，则JSON_ARRAYAGG使用文字转义序列\\表示该字符。
• 单引号：当string-expr值包含一个单引号作为文字字符时， SQL要求必须将此字符作为两个单引号字符(''进行双倍转义。JSON_ARRAYAGG将此字符表示为单引号字符‘。

最大JSON数组大小

默认的JSON_ARRAYAGG返回类型是VARCHAR(8192)。
这个长度包括JSON数组格式化字符以及字段数据字符。
如果预期返回的值将需要大于8192，可以使用CAST函数指定一个更大的返回值。
例如CAST(JSON_ARRAYAGG(value)) AS VARCHAR(12000))。
如果实际返回的JSON数组长于JSON_ARRAYAGG返回类型长度，IRIS将在返回类型长度处截断JSON数组，而不会发出错误。
因为截断JSON数组会删除其关闭的]字符，这使得返回值无效。

JSON_ARRAYAGG 和 %SelectMode

可以使用%SelectMode属性为JSON数组中的元素指定数据显示值:0=Logical(默认值)，1=ODBC, 2= display。
如果string-expr包含一个%List结构，则元素以ODBC模式表示，用逗号分隔，在逻辑和显示模式中以%List格式字符表示，用\转义序列表示。

JSON_ARRAYAGG和ORDER BY

JSON_ARRAYAGG函数将表中多行列的值组合成一个包含元素值的JSON数组。
因为在计算所有聚合字段之后，查询结果集中应用了一个ORDER BY子句，所以ORDER BY不能直接影响这个列表中的值序列。
在某些情况下，JSON_ARRAYAGG结果可能是按顺序出现的，但是不应该依赖于这种顺序。
在给定聚合结果值中列出的值不能显式排序。

相关的聚合函数

• LIST返回一个逗号分隔的值列表。
• %DLIST返回一个包含每个值的元素的IRIS列表。
• XMLAGG返回一个串接的值字符串。

示例

下面的嵌入式SQL示例返回一个主机变量，该变量包含示例的Home_State列中所有值的JSON数组。
以字母A开头的人名表:

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

SELECT JSON_ARRAYAGG(CTLOC_Desc) FROM CT_Loc

image.png

注意，这个JSON数组包含重复的值。

下面的动态SQL示例返回一个主机变量，该变量包含样本的Home_State列中所有不同(唯一)值的JSON数组。
以字母A开头的人名表:

ClassMethod JsonArrayagg()
{
    s myquery = 2
    s myquery(1) = "SELECT JSON_ARRAYAGG(DISTINCT Home_State) AS DistinctStates "
    s myquery(2) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'A'"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

下面的SQL示例为每个州创建了一个JSON数组，其中包含在Home_City列中找到的所有值，以及按州列出的这些城市值的计数。
每个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

更有用的是一个JSON数组的所有不同的值，发现在Home_City列为每个州，如下所示的动态SQL示例:

ClassMethod JsonArrayagg1()
{
    s myquery = 4
    s myquery(1) = "SELECT Home_State,COUNT(DISTINCT Home_City) AS DistCityCount,"
    s myquery(2) = "COUNT(Home_City) AS TotCityCount,"
    s myquery(3) = "JSON_ARRAYAGG(DISTINCT Home_City) AS ArrayDistCities "
    s myquery(4) = "FROM Sample.Person GROUP BY Home_State"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

注意，这个示例返回每个州的不同城市名称和总城市名称的整数计数。

下面的动态SQL示例使用%SelectMode属性为DOB日期字段返回的JSON值数组指定ODBC显示模式:

ClassMethod JsonArrayagg2()
{
    s myquery = 2
    s myquery(1) = "SELECT JSON_ARRAYAGG(DOB) AS DOBs "
    s myquery(2) = "FROM Sample.Person WHERE Name %STARTSWITH 'A'"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

下面的动态SQL示例使用%FOREACH关键字。
它为每个不同的Home_State返回一行，其中包含该Home_State的年龄值的JSON数组。

ClassMethod JsonArrayagg3()
{
    s myquery = 3
    s myquery(1) = "SELECT DISTINCT Home_State,"
    s myquery(2) = "JSON_ARRAYAGG(Age %FOREACH(Home_State)) AgesForState "
    s myquery(3) = "FROM Sample.Person WHERE Home_State %STARTSWITH 'M'"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}

下面的动态SQL示例使用%AFTERHAVING关键字。
它为每个Home_State返回一行，其中至少包含一个满足HAVING子句条件的Name值(以“M”开头的名称)。
第一个JSON_ARRAYAGG函数返回一个包含该状态所有名称的JSON数组。
第二个JSON_ARRAYAGG函数返回的JSON数组只包含满足HAVING子句条件的名称:

ClassMethod JsonArrayagg4()
{
    s myquery = 4
    s myquery(1) = "SELECT Home_State,JSON_ARRAYAGG(Name) AS AllNames,"
    s myquery(2) = "JSON_ARRAYAGG(Name %AFTERHAVING) AS HavingClauseNames "
    s myquery(3) = "FROM Sample.Person GROUP BY Home_State "
    s myquery(4) = "HAVING Name LIKE 'M%' ORDER BY Home_State"
    s tStatement = ##class(%SQL.Statement).%New()
    s tStatement.%SelectMode=1
    s qStatus = tStatement.%Prepare(.myquery)
    if qStatus'=1 {
        w "%Prepare failed:" 
        d $System.Status.DisplayError(qStatus) 
        q
    }
    s rset = tStatement.%Execute()
    d rset.%Display()
    w !,"End of data"
}