SQL Server 2016中ISJSON函数对VARCHAR(8000)长JSON校验异常

现象描述 #

在SQL Server 2016（SP3）企业版中，将两个结构合法的JSON字符串定义为VARCHAR(8000)类型，长度分别为2484和4294。调用ISJSON()函数校验时：

• 长度2484的短JSON返回1（校验有效）
• 长度4294的长JSON返回0（校验无效）
  但将长JSON转换为VARCHAR(MAX)后，ISJSON()返回1（校验有效）。

测试代码 #

DECLARE @parameter VARCHAR(8000)='{"ExpressionsArray":[{"Priority":2,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":11,"AttributeFieldName":"STR101","OperatorId":"14","OperatorValue":"Contains","Value":"ci","FormatType":"text"}],"Value":"city good2"},{"Priority":3,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":12,"AttributeFieldName":"FIELD339","OperatorId":"11","OperatorValue":"Between","Value":"70,80","FormatType":"between numbers"}],"Value":"very good"},{"Priority":4,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":13,"AttributeFieldName":"STR1","OperatorId":"11","OperatorValue":"Between","Value":"2022-08-02,2022-08-05","FormatType":"between dates"}],"Value":"date between 1"},{"Priority":5,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":14,"AttributeFieldName":"STR11","OperatorId":"5","OperatorValue":"Equals","Value":"lan","FormatType":"text"}],"Value":"lan"},{"Priority":6,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":15,"AttributeFieldName":"STR6","OperatorId":"26","OperatorValue":"This week","Value":"","FormatType":"disable"}],"Value":"This week date"},{"Priority":7,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":16,"AttributeFieldName":"STR7","OperatorId":"19","OperatorValue":"Today","Value":"","FormatType":"disable"}],"Value":"Today - date"},{"Priority":8,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":17,"AttributeFieldName":"STR9","OperatorId":"18","OperatorValue":"Tomorrow","Value":"","FormatType":"disable"}],"Value":"Tomorrow: date"},{"Priority":9,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":18,"AttributeFieldName":"STR5","OperatorId":"20","OperatorValue":"Yesterday","Value":"","FormatType":"disable"}],"Value":"Yesterday -> date"},{"Priority":10,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":19,"AttributeFieldName":"FIELD1","OperatorId":"7","OperatorValue":">","Value":"8","FormatType":"number"}],"Value":"Number >5 $ test"},{"Priority":11,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":20,"AttributeFieldName":"STR0","OperatorId":"5","OperatorValue":"Equals","Value":"cat","FormatType":"text"}],"Value":"String & Equals"},{"Priority":12,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":21,"AttributeFieldName":"FIELD2","OperatorId":"5","OperatorValue":"=","Value":"12","FormatType":"number"}],"Value":"number = 12"}],"ElseValue":"testik","Format":4,"DisplayName":"Cripto Test","Description":"desc: Cripto Test","FieldName":" ","PublishStatus":"NotPublished","Name":"CriptoTest","Type":"conditional","AttributeBaseType":"string","IsPersonalization":false}'
DECLARE @parameter1 VARCHAR(8000)='{"ExpressionsArray":[{"Priority":1,"ShowComplexExpression":false,"ComplexExpression":"@1 and @2 and @3 and @4 and @5 and @6 and @7 and @8 and @9 and @10","Conditions":[{"Position":1,"AttributeFieldName":"STR101","OperatorId":"14","OperatorValue":"Contains","Value":"ci","FormatType":"text"},{"Position":2,"AttributeFieldName":"STR13","OperatorId":"11","OperatorValue":"Between","Value":"2022-08-08,2022-08-12","FormatType":"between dates"},{"Position":3,"AttributeFieldName":"FIELD353","OperatorId":"11","OperatorValue":"Between","Value":"15,19","FormatType":"between numbers"},{"Position":4,"AttributeFieldName":"STR0","OperatorId":"14","OperatorValue":"Contains","Value":"cate","FormatType":"text"},{"Position":5,"AttributeFieldName":"FIELD362","OperatorId":"6","OperatorValue":"<>","Value":"5","FormatType":"number"},{"Position":6,"AttributeFieldName":"STR28","OperatorId":"13","OperatorValue":"Ends with","Value":"a","FormatType":"text"},{"Position":7,"AttributeFieldName":"STR15","OperatorId":"21","OperatorValue":"One of","Value":"tt","FormatType":"text"},{"Position":8,"AttributeFieldName":"FIELD522","OperatorId":"10","OperatorValue":"<=","Value":"7","FormatType":"number"},{"Position":9,"AttributeFieldName":"FIELD649","OperatorId":"6","OperatorValue":"<>","Value":"5","FormatType":"number"},{"Position":10,"AttributeFieldName":"STR3","OperatorId":"14","OperatorValue":"Contains","Value":"bobo","FormatType":"text"}],"Value":"city good"},{"Priority":2,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":11,"AttributeFieldName":"STR101","OperatorId":"14","OperatorValue":"Contains","Value":"ci","FormatType":"text"}],"Value":"city good2"},{"Priority":3,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":12,"AttributeFieldName":"FIELD339","OperatorId":"11","OperatorValue":"Between","Value":"70,80","FormatType":"between numbers"}],"Value":"very good"},{"Priority":4,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":13,"AttributeFieldName":"STR1","OperatorId":"11","OperatorValue":"Between","Value":"2022-08-02,2022-08-05","FormatType":"between dates"}],"Value":"date between 1"},{"Priority":5,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":14,"AttributeFieldName":"STR11","OperatorId":"5","OperatorValue":"Equals","Value":"lan","FormatType":"text"}],"Value":"lan"},{"Priority":6,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":15,"AttributeFieldName":"STR6","OperatorId":"26","OperatorValue":"This week","Value":"","FormatType":"disable"}],"Value":"This week date"},{"Priority":7,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":16,"AttributeFieldName":"STR7","OperatorId":"19","OperatorValue":"Today","Value":"","FormatType":"disable"}],"Value":"Today - date"},{"Priority":8,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":17,"AttributeFieldName":"STR9","OperatorId":"18","OperatorValue":"Tomorrow","Value":"","FormatType":"disable"}],"Value":"Tomorrow: date"},{"Priority":9,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":18,"AttributeFieldName":"STR5","OperatorId":"20","OperatorValue":"Yesterday","Value":"","FormatType":"disable"}],"Value":"Yesterday -> date"},{"Priority":10,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":19,"AttributeFieldName":"FIELD1","OperatorId":"7","OperatorValue":">","Value":"8","FormatType":"number"}],"Value":"Number >5 $ test"},{"Priority":11,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":20,"AttributeFieldName":"STR0","OperatorId":"5","OperatorValue":"Equals","Value":"cat","FormatType":"text"}],"Value":"String & Equals"},{"Priority":12,"ShowComplexExpression":false,"ComplexExpression":"@1","Conditions":[{"Position":21,"AttributeFieldName":"FIELD2","OperatorId":"5","OperatorValue":"=","Value":"12","FormatType":"number"}],"Value":"number = 12"}],"ElseValue":"testik","Format":4,"DisplayName":"Cripto Test","Description":"desc: Cripto Test","FieldName":" ","PublishStatus":"NotPublished","Name":"CriptoTest","Type":"conditional","AttributeBaseType":"string","IsPersonalization":false}'
SELECT ISJSON(@parameter) -- @parameter长度2848 - 返回1 - 正常
SELECT ISJSON(@parameter1)  -- @parameter长度4294 返回0 - 异常
SELECT ISJSON(CAST(@parameter1 AS VARCHAR(max)) ) -- 返回1 - 正常

原因分析 #

这是SQL Server 2016版本中ISJSON()函数的已知问题：

• 当传入非MAX类型的字符串参数（如VARCHAR(8000)）时，函数内部的JSON解析引擎存在缓冲区长度限制，当JSON内容长度超过该阈值时，解析会提前终止，导致返回无效结果。
• 而VARCHAR(MAX)类型使用的是另一套解析逻辑，不受该缓冲区限制，因此能正确解析完整的JSON内容。

该问题在SQL Server 2017及后续版本中已被修复。

解决方案 #

• 推荐方案：直接使用VARCHAR(MAX)或NVARCHAR(MAX)存储JSON数据，从根源避免长度限制带来的解析问题。
• 临时 workaround：如果必须使用VARCHAR(8000)类型，可先将数据转换为MAX类型后再调用ISJSON()，例如：ISJSON(CAST(json_column AS VARCHAR(MAX)))。
• 彻底解决：升级到SQL Server 2017及以上版本，修复该解析引擎的限制问题。

内容的提问来源于stack exchange，提问作者Oren