SQLServer2022新特性JSON_ARRAY从零个或更多表达式中构造 JSON 数组文本

参考官方文档
https://learn.microsoft.com/en-us/sql/t-sql/functions/json-array-transact-sql?view=sql-server-ver16

1、本文内容

语法
参数
返回值
示例

适用于：

SQL Server 2022 (16.x)
Azure SQL 数据库
Azure SQL 托管实例

从零个或更多表达式中构造 JSON 数组文本。

2、语法

JSON_ARRAY ( [ <json_array_value> [,...n] ] [ <json_null_clause> ]  )  

<json_array_value> ::= value_expression

<json_null_clause> ::=
      NULL ON NULL
    | ABSENT ON NULL

3、参数

json_array_value
是定义 JSON 数组中元素值的表达式。

json_null_clause
可用于控制 JSON_OBJECT 函数在 value_expression 为 NULL 时的行为。在生成 JSON 数组中的元素值时，选项 NULL ON NULL 将 SQL NULL 值转换为 JSON null 值。 如果值为 NULL，选项 ABSENT ON NULL 将省略 JSON 数组中的元素。此选项的默认设置为 ABSENT ON NULL。

4、返回值

返回 nvarchar(max) 类型的有效 JSON 数组字符串。

5、示例

5.1、返回空 JSON 数组

以下示例返回空 JSON 数组。

SELECT JSON_ARRAY() AS return_json;

return_json
--------------------------------------------------------------
{}

(1 行受影响)

5.2、返回具有四个元素的 JSON 数组

以下示例返回具有四个元素的 JSON 数组。

SELECT JSON_ARRAY(N'First line',N'第一行', N'Second line',2) AS return_json;

return_json
------------------------------------------------------------------
["First line","第一行","Second line",2]

(1 行受影响)

5.3、返回三个元素的 JSON 数组

以下示例返回具有三个元素的 JSON 数组，因为其中一个输入值为 NULL。由于省略 json_null_clause 且该选项的默认值为 ABSENT ON NULL，所以其中一个输入的 NULL 值不会被转换为 JSON 空值。

-- 默认值为 ABSENT ON NULL ,所以以下两条SQL执行效果一样
SELECT JSON_ARRAY('First', 1, NULL, 2) AS return_json;
-- OR 
SELECT JSON_ARRAY('First', 1, NULL, 2 ABSENT ON NULL) AS return_json;

-- result
return_json
--------------------------------------------------------------------
["First",1,2]

(1 行受影响)

5.4、返回四个元素的 JSON 数组

返回具有四个元素的 JSON 数组，指定 NULL ON NULL 选项是为了让输入中的任意 SQL NULL 值在 JSON 数组中转换为 JSON 空值。

SELECT JSON_ARRAY('First', 1, NULL, 2 NULL ON NULL) AS return_json;

return_json
--------------------------------------------------------------------
["First",1,null,2]

(1 行受影响)

5.5、返回包含JSON字符串和JSON 对象的两个元素的JSON数组

以下示例返回具有两个元素的 JSON 数组。一个元素包含 JSON 字符串，另一个元素包含 JSON 对象。

SELECT JSON_ARRAY('1001',JSON_OBJECT('name':'SQLNode1','age':28)) AS return_json;

return_json
-------------------------------------------------------------------------------
["1001",{"name":"SQLNode1","age":28}]

(1 行受影响)

5.6、返回具有三个元素的 JSON 数组包含JSON字符串，对象及数组

以下示例返回具有三个元素的 JSON 数组。一个元素包含 JSON 字符串，另一个元素包含 JSON 对象，而另一个元素包含 JSON 数组。

--以下示例返回具有三个元素的 JSON 数组。 
-- 一个元素包含 JSON 字符串，
-- 另一个元素包含 JSON 对象，
-- 而另一个元素包含 JSON 数组。
SELECT JSON_ARRAY('1002',JSON_OBJECT('name':'SQLNode2','age':29), JSON_ARRAY('One', 1, NULL, 2 NULL ON NULL)) AS return_json;

return_json
--------------------------------------------------------------------------------------
["1002",{"name":"SQLNode2","age":29},["One",1,null,2]]

(1 行受影响)

5.7、输入指定为变量或SQL表达式返回JSON 数组

以下示例返回 JSON 数组，其输入指定为变量或 SQL 表达式。

DECLARE @id_value NVARCHAR(64) = NEWID();
SELECT JSON_ARRAY(1, @id_value, (SELECT @@SPID),(SELECT GETDATE())) AS return_json;

return_json
--------------------------------------------------------------------------------
[1,"BA5E3B36-E12E-4F4E-84D7-584EEB3C43CE",68,"2024-05-29T10:52:51.860"]

(1 行受影响)

5.8、返回查询中每行的 JSON 数组

以下示例返回查询中每行的 JSON 数组。

CREATE TABLE Persons (  
    ID INT PRIMARY KEY,  
    Name NVARCHAR(50),  
    Age INT  
);  
  
INSERT INTO Persons VALUES (1, 'John Doe', 30);  
INSERT INTO Persons VALUES (2, 'Jane Smith', 25);  

SELECT ID,Name,Age FROM Persons;
  
SELECT   
    ID,  
    JSON_ARRAY(Name,Age) AS PersonJson  
FROM Persons;

ID          PersonJson
----------- ---------------------------------------------------------------
1           ["John Doe",30]
2           ["Jane Smith",25]

(2 行受影响)

6、相关内容

SQL Server 中的 JSON 数据

参考官方文档
https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16