第六十八章 SQL函数 JSON_ARRAY
以JSON数组形式返回数据的转换函数。
大纲
JSON_ARRAY(expression [,expression][,...] [NULL ON NULL | ABSENT ON NULL])
参数
expression- 表达式或逗号分隔的表达式列表。这些表达式可以包括列名、聚合函数、算术表达式、文字和文字NULL。ABSENT ON NULLNULL ON NULL- 可选-指定如何在返回的JSON数组中表示空值的关键字短语。NULL ON NULL(缺省值)表示带有单词NULL(未引号)的NULL(缺少)数据。在NULL上不存在将从JSON数组中省略空数据；它不会保留占位符逗号。此关键字短语对空字符串值没有影响。
描述
Json_array接受表达式或(更常见的)逗号分隔的表达式列表，并返回包含这些值的JSON数组。Json_array可以在SELECT语句中与其他类型的SELECT-Items结合使用。可以在可以使用SQL函数的其他位置指定json_array，例如在WHERE子句中。
返回的JSON数组格式如下：
[ element1 , element2 , element3 ]
Json_array以字符串(用双引号括起来)或数字形式返回每个数组元素值。数字以规范格式返回。数字字符串以文字形式返回，用双引号括起来。所有其他数据类型(例如，DATE或$LIST)都作为字符串返回。
Json_array不支持将星号(*)语法作为指定表中所有字段的方式。它支持COUNT(*)聚合函数。
返回的JSON数组列被标记为表达式(默认情况下)；可以为JSON_ARRAY指定列别名。
选择模式和归类
当前%SelectMode属性确定返回的JSON数组值的格式。通过更改Select Mode，所有Date和%List元素都以该Select Mode格式的字符串包含在JSON数组中。
可以通过将格式转换函数(%EXTERNAL、%INTERNAL、%ODBCIN、%ODBCOUT)应用于JSON_ARRAY内的各个字段名来覆盖当前的选择模式。将格式转换函数应用于JSON_ARRAY没有任何效果，因为JSON数组的元素是字符串。
可以将归类函数应用于JSON_ARRAY内的单个字段名或整个JSON_ARRAY：
- 应用于
JSON_ARRAY的排序函数在JSON数组格式化之后应用排序规则。因此，%SQLUPPER(JSON_ARRAY(f1，f2))将所有JSON数组元素值转换为大写。%SQLUPPER(JSON_ARRAY(f1，f2))在JSON数组之前插入一个空格，而不是在数组元素之前插入一个空格；因此它不会强制将数字解析为字符串。 - 应用于
JSON_ARRAY中的元素的排序规则函数将应用该排序规则。因此，JSON_ARRAY(‘abc’，%SQLUPPER(‘abc’))返回["Abc"," ABC"](注意前导空格)；而JSON_ARRAY(007，%SQLSTRING(007))返回[7," 7"]。由于%SQLUPPER会在值之前插入一个空格，因此通常最好指定大小写转换函数，如LCASE或UCASE。可以将排序规则应用于元素和整个数组：%SQLUPPER(JSON_ARRAY('Abc',%SQLSTRING('Abc'))) returns ["ABC"," ABC"]
ABSENT ON NULL
如果指定可选的ACESING ON NULL关键字短语，则JSON数组中不包括NULL(或NULL文字)列值。JSON数组中不包括占位符。这可能会导致JSON数组具有不同数量的元素。例如，下面的程序返回JSON数组，其中对于某些记录，第三个数组元素是Age，对于其他记录，第三个数组元素是FavoriteColors：
SELECT JSON_ARRAY(%ID,Name,FavoriteColors,Age ABSENT ON NULL) FROM Sample.Person
如果未指定关键字短语，则NULL的默认值为NULL：NULL由单词NULL(未用引号分隔)表示为逗号分隔的数组元素。因此，JSON_ARRAY函数返回的所有JSON数组都将具有相同数量的数组元素。
示例
下面的示例应用JSON_ARRAY来格式化包含逗号分隔的字段值列表的JSON数组：
SELECT TOP 3 JSON_ARRAY(%ID,Name,Age,Home_State) FROM Sample.Person
下面的示例应用JSON_ARRAY使用包含名称字段值的单个元素格式化JSON数组：
SELECT TOP 3 JSON_ARRAY(Name) FROM Sample.Person
下面的示例应用JSON_ARRAY来格式化包含文字和字段值的JSON数组：
SELECT TOP 3 JSON_ARRAY('Employee from',%TABLENAME,Name,SSN) FROM Sample.Employee
下面的示例应用JSON_ARRAY来格式化包含空值和字段值的JSON数组：
SELECT JSON_ARRAY(Name,FavoriteColors) FROM Sample.Person
WHERE Name %STARTSWITH 'S'
下面的示例应用JSON_ARRAY来格式化包含联接表中的字段值的JSON数组：
SELECT TOP 3 JSON_ARRAY(E.%TABLENAME,E.Name,C.%TABLENAME,C.Name)
FROM Sample.Employee AS E,Sample.Company AS C
下面的动态SQL示例设置ODBC %SelectMode，它确定如何表示所有字段，包括JSON数组值。该查询通过应用%EXTERNAL FORMAT-CONVERSION函数覆盖特定JSON数组元素的此选择模式：
ClassMethod JsonArray()
{
s myquery = 3
s myquery(1) = "SELECT TOP 8 DOB,JSON_ARRAY(Name,DOB,FavoriteColors) AS ODBCMode, "
s myquery(2) = "JSON_ARRAY(Name,DOB,%EXTERNAL(DOB),%EXTERNAL(FavoriteColors)) AS ExternalTrans "
s myquery(3) = "FROM Sample.Person"
s tStatement = ##class(%SQL.Statement).%New()
s tStatement.%SelectMode=1
w "SelectMode is ODBC",!
s qStatus = tStatement.%Prepare(.myquery)
s rset = tStatement.%Execute()
if rset.%SQLCODE=0 {
w !,"Executed query",!
} else {
s badSQL=##class(%Exception.SQL).%New(,rset.%SQLCODE, , rset.%Message)
}
d rset.%Display()
w !,"End of data"
}
DHC-APP> d ##class(PHA.TEST.SQLCommand).JsonArray()
SelectMode is ODBC
Executed query
DOB ODBCMode ExternalTrans
1990-04-25 ["yaoxin","1990-04-25","Red,Orange,Yellow"] ["yaoxin","1990-04-25","04/25/1990","Red\r\nOrange\r\nYellow"]
["xiaoli",null,null] ["xiaoli",null,null,null]
2014-01-02 ["姚鑫","2014-01-02",null] ["姚鑫","2014-01-02","01/02/2014 ",null]
2014-01-02 ["姚鑫","2014-01-02",null] ["姚鑫","2014-01-02","01/02/2014 ",null]
1978-01-28 ["姚鑫","1978-01-28",null] ["姚鑫","1978-01-28","01/28/1978 ",null]
["姚鑫",null,"Red,Orange,Yellow,Green"] ["姚鑫",null,null,"Red\r\nOrange \r\nYellow\r\nGreen"]
["姚鑫",null,"Red,Orange,Yellow,Green,Green"] ["姚鑫",null,null,"Red\r \nOrange\r\nYellow\r\nGreen\r\nGreen"]
["Isaacs,Roberta Z.",null,"Red,Orange,Yellow,Green,Yellow"] ["Isaacs,Roberta Z.",null,null,"Red\r\nOrange\r\nYellow\r\nGreen\r\nYellow"]
8 Rows(s) Affected
End of data
下面的示例在WHERE子句中使用JSON_ARRAY在不使用OR语法的情况下对多列执行CONTAINS测试：
SELECT Name,Home_City,Home_State FROM Sample.Person
WHERE JSON_ARRAY(Name,Home_City,Home_State) [ 'X'