使用JSON_QUERY()添加正则表达式模式时遇JSON格式错误

我有一个存储正则表达式详情的表，想要生成JSON数组对象。使用JSON_QUERY()构建JSON时遇到以下错误：

"JSON文本格式不正确。发现意外字符''。"
只有当正则表达式模式中包含反斜杠\时才会触发这个错误。

示例数据：

DECLARE @RegExData AS TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, label VARCHAR(100), ValidationName VARCHAR(100), ValidationPattern VARCHAR(100))

INSERT @RegExData(label, ValidationName, ValidationPattern)
VALUES('LABEL1', 'NumberOnlyEXP','/^[0-9\s]*$/')

INSERT @RegExData(label, ValidationName, ValidationPattern)
VALUES('LABEL1', 'DecimalEXP','/^(\d+(\.\d+)?)$/')

INSERT @RegExData(label, ValidationName, ValidationPattern)
VALUES('LABEL2', 'DecimalWithZeroEXP','/^(\d+(\.\d+)?)$/')

INSERT @RegExData(label, ValidationName, ValidationPattern)
VALUES('LABEL2', 'AlphabetOnlyEXP',' /^[a-zA-Z]*$/')

报错语句：

SELECT
    JSON_QUERY('[' + STRING_AGG('{"ValidationName":' +  CHAR(34) + CAST(ValidationName AS VARCHAR(100)) +  CHAR(34) +
        ',' + '"ValidationPattern":' + CHAR(34) + CAST(ValidationPattern AS VARCHAR(100))+ CHAR(34) +
    '}' , ',') + ']') AS data
FROM @RegExData
FOR JSON PATH

错误根源是JSON格式要求反斜杠必须转义为双反斜杠\\，你手动拼接JSON字符串时没有处理反斜杠的转义，导致JSON解析失败。

以下是两种可靠的解决方法：

方法1：用STRING_ESCAPE转义特殊字符 #

在拼接ValidationPattern时，通过STRING_ESCAPE函数对值进行JSON转义，确保反斜杠等特殊字符符合JSON格式要求：

SELECT
    JSON_QUERY('[' + STRING_AGG('{"ValidationName":' +  CHAR(34) + STRING_ESCAPE(ValidationName, 'json') +  CHAR(34) +
        ',' + '"ValidationPattern":' + CHAR(34) + STRING_ESCAPE(ValidationPattern, 'json') + CHAR(34) +
    '}' , ',') + ']') AS data
FROM @RegExData
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

注意添加WITHOUT_ARRAY_WRAPPER，避免外层FOR JSON PATH重复套数组导致结构冗余。

方法2：直接用FOR JSON自动生成（推荐） #

完全不需要手动拼接JSON，利用SQL Server的FOR JSON功能自动处理转义和格式，代码更简洁且不易出错：

SELECT
    ValidationName,
    ValidationPattern
FROM @RegExData
FOR JSON PATH, ROOT('data')

如果需要按label分组生成嵌套结构，可使用：

SELECT
    label,
    (SELECT ValidationName, ValidationPattern FROM @RegExData r2 WHERE r2.label = r1.label FOR JSON PATH) AS validations
FROM @RegExData r1
GROUP BY label
FOR JSON PATH

这两种方法都会自动处理正则表达式中的反斜杠转义，生成符合标准的JSON格式。

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