Add RegEx pattern inside JSON string using JSON_QUERY()

Question

I have RegEx details table and I want to make array object. I am trying to make JSON using JSON_QUERY() then I getting error like:

"JSON text is not properly formatted. Unexpected character '"' is found".

It's only getting when I use RegEx pattern (which has \ slash) as JSON property value.

Sample data:

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]*$/')
    

Statement with error:

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) +
    '}' , ',') + ']')SELECT AS* data
FROM @RegExData
FOR JSON PATH

Answer 1

I tried a new code, but there is a double \ (backslash) issue in RegEx pattern. Here is sample data and code with output.

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 * FROM @RegExData

SELECT LABEL
 , (SELECT t1.ValidationName AS ' label', t1.ValidationPattern AS ' value'
    FROM @RegExData t1
    WHERE  t1.label = t.label
    FOR JSON PATH
    ) as data
FROM @RegExData t
GROUP BY label
FOR JSON AUTO

OUTPUT