Question 290424 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Add RegEx pattern inside JSON string using JSON_QUERY()

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.

    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-9s]*$/')
    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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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


  2. 0

    If I understand the question correctly, you may try to generate the expected output with the following statement:

    SELECT DISTINCT d.label, j.data
FROM @RegExData d
CROSS APPLY (
   SELECT ValidationName, ValidationPattern
   FROM @RegExData
   WHERE label = d.label
   FOR JSON AUTO
) j (data)
FOR JSON PATH

    In this case, if the source data contains special characters, the FOR JSON clause escapes them in the JSON output with . The result from the statement execution is:

    [
   {"label":"LABEL1","data":[{"ValidationName":"NumberOnlyEXP","ValidationPattern":"/^[0-9s]*$/"},{"ValidationName":"DecimalEXP","ValidationPattern":"/^(d+(.d+)?)$/"}]},
   {"label":"LABEL2","data":[{"ValidationName":"DecimalWithZeroEXP","ValidationPattern":"/^(d+(.d+)?)$/"}]}
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search