SQLServer2022新特性JSON_OBJECT从零个或多个表达式中构造JSON对象

SQLServer2022新特性JSON_OBJECT从零个或多个表达式中构造 JSON 对象文本

参考中文官方文档
https://learn.microsoft.com/zh-cn/sql/t-sql/functions/json-object-transact-sql?view=sql-server-ver16

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

1、本文内容

语法
参数
返回值
示例

适用于：

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

从零个或多个表达式中构造 JSON 对象文本。

2、语法

JSON_OBJECT ( [ <json_key_value> [,...n] ] [ json_null_clause ] )

<json_key_value> ::= json_key_name : value_expression

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

3、参数

json_key_name
是定义 JSON 键名称值的字符表达式。

value_expression 是定义 JSON 键值的表达式。

json_null_clause
可用于控制 JSON_OBJECT 函数在 value_expressio 为 NULL 时的行为。在生成 JSON 键值时，选项 NULL ON NULL 将 SQL NULL 值转换为 JSON NUL 值。 如果值为 ABSENT ON NULL，选项 NULL 将省略整个键。 此选项的默认设置为 NULL ON NULL。

4、返回值

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

若要了解 JSON_OBJECT 函数的输出内容，请查看以下文章：

FOR JSON 如何将 SQL Server 数据类型转换为 JSON 数据类型 (SQL Server)
JSON_OBJECT 函数使用这篇 FOR JSON 文章中描述的规则在 JSON 输出中将 SQL 数据类型转换为 JSON 类型。

FOR JSON 如何转义特殊字符和控制字符 (SQL Server)
JSON_OBJECT 函数按照这篇 FOR JSON 文章中描述的方式在 JSON 输出中转义特殊字符和表示控制字符。

https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-converts-sql-server-data-types-to-json-data-types-sql-server?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-escapes-special-characters-and-control-characters-sql-server?view=sql-server-ver16

5、示例

5.1、返回空 JSON 对象

以下示例返回空 JSON 对象

SELECT JSON_OBJECT() AS return_json;

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

(1 行受影响)

5.2、返回包含两个键的 JSON 对象

以下示例返回包含两个键的 JSON 对象

DECLARE @name NVARCHAR(50) = 'SQLServer2022';  
DECLARE @age INT = 88;    
SELECT JSON_OBJECT('name':@name, 'age':@age) AS return_json;

return_json
-----------------------------------------------------------
{"name":"SQLServer2022","age":88}

(1 行受影响)

5.3、返回包含一个键的 JSON 对象，其中一个键的值为 NULL

以下示例返回包含一个键的 JSON 对象，因为其中一个键的值为 NULL，并且指定了 ABSENT ON NULL 选项。

SELECT JSON_OBJECT('name':'value', 'type':NULL ABSENT ON NULL) AS return_json;

return_json
--------------------------------------------------
{"name":"value"}

(1 行受影响)

以下示例返回包含一个键的 JSON 对象，因为其中一个键的值为 NULL，并且指定了 NULL ON NULL 选项。

-- NULL ON NULL 选项 为默认值
SELECT JSON_OBJECT('name':'value', 'type':NULL) AS return_json;
-- OR 
SELECT JSON_OBJECT('name':'value', 'type':NULL NULL ON NULL) AS return_json;

return_json
----------------------------------------------------
{"name":"value","type":null}

(1 行受影响)

5.4、返回包含两个键的 JSON 对象包括字符串和JSON 数组

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

SELECT JSON_OBJECT('id':1, 'info':JSON_ARRAY('SQLNode1,DBadmin')) AS return_json;

return_json
--------------------------------------------------------
{"id":1,"info":["SQLNode1,DBadmin"]}

(1 行受影响)

5.5、返回包含两个键的 JSON 对象包含字符串和JSON 对象

以下示例返回包含两个键的 JSON 对象。一个键包含 JSON 字符串，另一个键包含 JSON 对象。

DECLARE @name NVARCHAR(50) = 'NBA Games';  
DECLARE @age INT = 30;  
SELECT JSON_OBJECT('id':'1001', 'info':JSON_OBJECT('name':@name, 'age':@age)) AS return_json;

return_json
-------------------------------------------------------------------
{"id":"1001","info":{"name":"NBA Games","age":30}}

(1 行受影响)

5.6、输入指定为变量或 SQL表达式返回JSON 对象

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

DECLARE 
  @id_key nvarchar(10) = N'id',
  @id_value nvarchar(64) = NEWID();

SELECT JSON_OBJECT
( 'username':USER_NAME(), 
  @id_key:@id_value, 
  'sid':(SELECT @@SPID),
  'sys_date':(SELECT GETDATE())
) AS return_json;

return_json
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"username":"dbo","id":"7105CD3C-041F-4CEC-AA98-0621F008C3B8","sid":68,"sys_date":"2024-05-29T09:47:01.893"}

(1 行受影响)

5.7、在查询中对每行返回一个 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;
ID          Name                                               Age
----------- -------------------------------------------------- -----------
1           John Doe                                           30
2           Jane Smith                                         25

(2 行受影响) 
-- Result  
SELECT   
    ID,  
    JSON_OBJECT('name':Name, 'age':Age) AS PersonJson  
FROM Persons;

ID          personJson
----------- ---------------------------------------------------
1           {"name":"John Doe","age":30}
2           {"name":"Jane Smith","age":25}

(2 行受影响)