SQLServer2022 ISJSON新特性增强json_type

SQLServer2022 ISJSON新特性增强，引入json_type_constraint参数，检查json类型

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

1、本文内容

语法
参数
返回值
注解

适用于：
SQL Server 2016 (13.x) 及更高版本
Azure SQL 数据库
Azure SQL 托管实例

Transact-SQL 语法约定
https://learn.microsoft.com/zh-cn/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver15

2、语法

ISJSON ( expression [, json_type_constraint] )

3、参数

expression
要测试的字符串。

json_type_constraint
指定要签入输入的 JSON 类型。有效值为 VALUE、ARRAY、OBJECT 或 SCALAR。在 SQL Server 2022 (16.x) 中引入。

备注
Azure Synapse Analytics 专用池不支持参数 json_type_constraint

4、返回值

如果字符串包含有效 JSON，则返回 1；否则，返回 0。如果 expression 为 NULL，则返回 NULL。

如果语句省略 json_type_constraint，函数将测试输入是否为有效的 JSON 对象或数组，如果是，则返回 1；否则返回 0。

如果指定了 json_type_constraint ，该函数将检查 JSON 类型，如下所示：
值说明

VALUE 测试有效的 JSON 值。这可以是 JSON 对象、数组、数字、字符串或三个字面量值之一（false、true、null）
ARRAY 测试有效的 JSON 数组
OBJECT 测试有效的 JSON 对象
SCALAR 测试有效的 JSON 标量 - 数字或字符串

json_type_constraint 值 SCALAR 可用于测试符合 IETF RFC 8259 的仅包含顶级 JSON 标量值的 JSON 文档。不包含顶级 JSON 标量值的 JSON 文档符合 IETF RFC 4627。

不返回错误。

5、注解

ISJSON 不检查在相同级别的键的唯一性。

6、示例

6.1、如果参数值包含有效 JSON

如果参数值 @param 包含有效 JSON，则下面的示例有条件地运行语句块。

6.1.1、参数值@param 包含有效 JSON

DECLARE @param varchar(256)
SET @param = N'[{"name": "John","skills": [ "SQL", "C#", "Azure" ]},{"name": "Jane","surname": "Doe"}
]'IF (ISJSON(@param) =1)  
BEGIN  PRINT  'the string contains valid JSON'
END
ELSE
BEGINPRINT 'the string does not contains valid JSON'
END-- Return value
the string contains valid JSON

在这里插入代码片

6.1.2、参数值@param 不包含有效 JSON

DECLARE @param varchar(256)
SET @param = N'
{"String_value": "John","DoublePrecisionFloatingPoint_value": 45,"DoublePrecisionFloatingPoint_value": 2.3456,"BooleanTrue_value": true,"BooleanFalse_value": false,"Null_value": null,"Array_value": ["a","r","r","a","y"],"Object_value": {"obj":"ect"}
}'IF (ISJSON(@param) =1)  
BEGIN  PRINT  'the string contains valid JSON'
END
ELSE
BEGINPRINT 'the string does not contains valid JSON'
END-- Return value
the string does not contains valid JSON

json_colJSON119">6.2、示例返回其列 json_col 包含有效 JSON 的行

下面的示例将返回其列 json_col 包含有效 JSON 的行

with cte_json as (
select 1001 as id,'[{"nameinfo":"sqlserver2022","age":29}]' as json_col
union all
select 1002 as id,'[{"nameinfo":"sqlserver2019","age":26}]' as json_col
union all
select 1003 as id,'{"nameinfo":"sqlserver2000",age:22}' as json_col
)
SELECT id, json_col
FROM cte_json
WHERE ISJSON(json_col) = 1id          json_col
----------- ---------------------------------------
1001        [{"nameinfo":"sqlserver2022","age":29}]
1002        [{"nameinfo":"sqlserver2019","age":26}](2 行受影响)

6.3、示例 3

下面的示例将返回其列 json_col 在顶级包含有效 JSON SCALAR 值的行。

with cte_json as (
select 1001 as id,'[{"nameinfo":"sqlserver2022","age":29}]' as json_col
union all
select 1002 as id,'[{"nameinfo":"sqlserver2019","age":26}]' as json_col
union all
select 1003 as id,'{"nameinfo":"sqlserver2000",age:22}' as json_col
union all
select 1004 as id,N'[{"name": "John","skills": [ "SQL", "C#", "Azure" ]},{"name": "Jane", "surname": "Doe"}]' as json_col 
)
SELECT id, json_col
FROM cte_json
WHERE ISJSON(json_col, SCALAR) = 1id          json_col
----------- ------------------------------------------------------------------------(0 行受影响)

json_type_constraintVALUE_165">6.4、示例返回 json_type_constraint类型为VALUE

下面的示例返回 1，因为输入是有效的 JSON 值 - true。

SELECT ISJSON('[{"name": "John","skills": [ "SQL", "C#", "Azure" ]},{"name": "Jane", "surname": "Doe"}]', VALUE) as ISJSON_VALUEISJSON_VALUE
------------
1

下面的示例返回 0，因为输入是无效的 JSON 值。

SELECT ISJSON('{"nameinfo":"sqlserver2000",age:22}', VALUE) as ISJSON_VALUEISJSON_VALUE
------------
0(1 行受影响)