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

@DeepThumar, what final output do you want to generate? The actual reason for this error is that JSON_QUERY() expects a valid JSON content. SELECT ValidationName, ValidationPattern FROM @RegExData FOR JSON PATH is probably an option here. — Zhorov
– Zhorov, Commented Nov 22, 2023 at 7:01
@Zhorov, I want this JSON to be inside an array. I want to receive the following output: [ { "label": "Max Fare Change Per Seat:", "data": [ { "value": 0, "label": "Percentage", "DefaultValue": "N/A", "ValidationType": "DecimalEXP", "ValidationPattern":"/^(\d+(\.\d+)?)$/" "MaxLength": "3" }, { "value": 0, "label": "Rupees", "DefaultValue": "N/A", "ValidationType": "NumberOnlyEXP", "ValidationPattern":"/^[0-9\s]*$/" "MaxLength": "10" } ] } ] — Deep Thumar
– Deep Thumar, Commented Nov 22, 2023 at 7:10

Deep Thumar · Accepted Answer · 2023-11-22 11:58:47Z

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

edited Nov 22, 2023 at 11:58

answered Nov 22, 2023 at 11:35

Deep Thumar

477 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlieface Over a year ago

It's correct. It's supposed to escape the \ because it has special meaning in JSON. When a parser reads it then it will be unescaped correctly Eg dbfiddle.uk/JILBNCl3

Collectives™ on Stack Overflow

Add RegEx pattern inside JSON string using JSON_QUERY()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related