Avoid escaping characters when converting from tabular data to json

Question

I have some problems converting tabular data to JSON using the FOR JSON PATH syntax:

If i do a standard query:

SELECT b.Name FROM dbo

I get results of the form: 12/5-A-1. I need this converted to JSON data without escaping the backslash character. However, when i convert it to JSON:

SELECT b.Name FROM dbo FOR JSON PATH, WITHOUT ARRAY_WRAPPER

the result is of the form: {"Name": "12\/5-A-1"}

How can i do this transformation without escaping the backslash character and get the result {"Name": "12/5-A-1"}?

Check if this is helpful: https://stackoverflow.com/questions/258757/escape-a-string-in-sql-server-so-that-it-is-safe-to-use-in-like-expression — Shail_bee, May 08 '19 at 06:59

score 1 · Accepted Answer · answered May 08 '19 at 09:49

One option is to use a common table expression to generate the json, and then simply use replace when selecting from the common table expression.

First, create and populate sample data (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    [Name] nvarchar(10)
)

INSERT INTO @T ([Name]) VALUES ('12/5-A-1');

The cte:

WITH CTE(Escaped) AS
(

    SELECT [Name]
    FROM @T
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)

The final select:

SELECT REPLACE(Escaped, '\/','/') As Result
FROM CTE

Result:

{"Name":"12/5-A-1"}

1 Answers1