SQLServer2022 ISJSON新特性增强json_type_constraint参数

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
BEGIN
  PRINT '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
BEGIN
  PRINT 'the string does not contains valid JSON'
END

-- Return value
the string does not contains valid JSON

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) = 1
 
id          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) = 1

id          json_col
----------- ------------------------------------------------------------------------

(0 行受影响)

6.4、示例返回 json_type_constraint类型为VALUE

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

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

ISJSON_VALUE
------------
1

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

SELECT ISJSON('{"nameinfo":"sqlserver2000",age:22}', VALUE) as ISJSON_VALUE

ISJSON_VALUE
------------
0

(1 行受影响)