Ndtyjky

I've created a stored procedure to pull data as a JSON object from my SQL Server database. All my data is relational and I'm trying to get it out as a JSON string.

Currently, I am able to get out a JSON string from SQL Server just fine, however this object ALWAYS includes escape characters (e.g. "{"field":"value"}). I'd like to pull the same JSON but without escaped characters. To test this I'm using some simple queries and getting them into .NET with a SqlDataAdapter using my stored procedure.

The thing that puzzles me is that when I run my query within SSMS, I never see any escape characters, but as soon as it's pulled a .NET application, the escape characters appear. I'd like to prevent this from happening and have my applications get only the unescaped JSON string.

I've tried several suggestions I've found during my research but nothing has produced my desired results. The changes I've seen (documented in MSDN and in other SO posts) have dealt with getting unescaped results, but only within SSMS and not within other applications.

What I've tried:

Simple Json query set to param and then using JSON_QUERY to select the param:

DECLARE @JSON varchar(max)



SET @JSON = (SELECT '{"Field":"Value"}' AS myJson FOR JSON PATH)



SELECT JSON_QUERY(@JSON) AS 'JsonResponse' FOR JSON PATH

This produces the following in a .NET application:

"[{"JsonResponse":{"Field":"Value"}}]"

This produces the following in SSMS:

[{"JsonResponse":[{"myJson":"{"Field":"Value"}"}]}]

Simple Json query without param using JSON_QUERY:

SELECT JSON_QUERY('{"Field":"Value"}') AS 'JsonResponse' FOR JSON PATH

This produces the following in a .NET application

"[{"JsonResponse":{"Field":"Value"}}]"

This produces the following in SSMS

[{"JsonResponse":{"Field":"Value"}}]

Simple Json query with temp tables using JSON_QUERY:

CREATE TABLE #temp(

    jsoncol varchar(255)

)



INSERT INTO #temp VALUES ('{"Field":"Value"}')



SELECT JSON_QUERY(jsoncol) AS 'JsonResponse' FROM #temp FOR JSON PATH



DROP TABLE #temp

This produces the following in a .NET application:

"[{"JsonResponse":{"Field":"Value"}}]"

This produces the following in SSMS:

[{"JsonResponse":{"Field":"Value"}}]

I'm lead to believe that there is no way to get out a JSON string from SQL Server without having the escaped characters. In case the examples above weren't enough, I've included my stored procedure here. Hopefully someone can point me in the right direction.

This is my stored procedure:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



ALTER PROCEDURE [dbo].[sp_GetRoles]



@USER_ID int



AS

BEGIN



DECLARE @JSON varchar(max)



SET @JSON = (SELECT DISTINCT src.SOURCE_ID AS [source_id]

,RTRIM(src.SOURCE_CODE) AS [source_code]

,src.SOURCE_LABEL AS [source_label]

,(



    SELECT srcapp.SOURCE_APPLICATION_ID AS [source_application_id]

    ,srcapp.APPLICATION_ID AS [application_id]

    ,app.APPLICATION_CODE AS [application_code]

    ,CASE WHEN srcappusr.ADMINISTRATOR_FLAG = 'Y' THEN 'true' ELSE 'false' END AS [is_administrator]

    FROM dbo.SOURCE_APPLICATION AS srcapp

    JOIN(SELECT APPLICATION_ID, APPLICATION_CODE FROM dbo.APPLICATION AS app WHERE RECORD_STATUS = 'A') app ON srcapp.APPLICATION_ID = app.APPLICATION_ID

    JOIN(SELECT SOURCE_APPLICATION_ID, USER_ID, ADMINISTRATOR_FLAG FROM dbo.SOURCE_APPLICATION_USER AS srcappusr WHERE RECORD_STATUS = 'A') srcappusr ON srcapp.SOURCE_APPLICATION_ID = srcappusr.SOURCE_APPLICATION_ID

    WHERE src.SOURCE_ID = srcapp.SOURCE_ID

    AND srcappusr.USER_ID = @USER_ID

    AND srcapp.RECORD_STATUS = 'A'

    FOR JSON PATH



) AS [source.applications]

,(



    SELECT env.ENVIRONMENT_ID AS [environment_id]

    ,RTRIM(env.ENVIRONMENT_CODE) AS [environment_code]

    ,CASE WHEN envusr.ADMINISTRATOR_FLAG = 'Y' THEN 'true' ELSE 'false' END AS [is_administrator]

    FROM dbo.ENVIRONMENT AS env

    JOIN(SELECT ENVIRONMENT_ID, USER_ID, ADMINISTRATOR_FLAG FROM dbo.ENVIRONMENT_USER AS envusr WHERE RECORD_STATUS = 'A') envusr ON env.ENVIRONMENT_ID = envusr.ENVIRONMENT_ID

    WHERE src.SOURCE_ID = env.SOURCE_ID

    AND envusr.USER_ID = @USER_ID

    AND env.RECORD_STATUS = 'A'

    FOR JSON PATH



) AS [source.environments]

FROM dbo.SOURCE AS src



LEFT JOIN(SELECT SOURCE_ID, SOURCE_APPlICATION_ID FROM dbo.SOURCE_APPLICATION AS srcapp WHERE RECORD_STATUS = 'A') srcapp ON src.SOURCE_ID = srcapp.SOURCE_ID

LEFT JOIN(SELECT SOURCE_APPLICATION_ID, USER_ID FROM dbo.SOURCE_APPLICATION_USER AS srcappusr WHERE RECORD_STATUS = 'A') srcappusr ON srcapp.SOURCE_APPLICATION_ID = srcappusr.SOURCE_APPLICATION_ID

LEFT JOIN(SELECT SOURCE_ID, ENVIRONMENT_ID FROM dbo.ENVIRONMENT AS env WHERE RECORD_STATUS = 'A') env ON src.SOURCE_ID = env.SOURCE_ID

LEFT JOIN(SELECT ENVIRONMENT_ID, USER_ID FROM dbo.ENVIRONMENT_USER envusr WHERE RECORD_STATUS = 'A') envusr ON env.ENVIRONMENT_ID = envusr.ENVIRONMENT_ID



WHERE 

(srcappusr.USER_ID = @USER_ID OR envusr.USER_ID = @USER_ID)

AND src.RECORD_STATUS = 'A'

FOR JSON PATH)



SELECT JSON_QUERY(@JSON) as 'JsonResponse'

END

This produces the following in a .NET application

"[{"source_id":1,"source_code":"1","source_label":"SOURCE1","source":{"applications":[{"source_application_id":1,"application_id":1,"application_code":"APP1","is_administrator":"true"}],"environments":[{"environment_id":1,"environment_code":"1","is_administrator":"true"}]}}]"

But produces the following in SSMS

[{"source_id":1,"source_code":"1","source_label":"SOURCE1","source":{"applications":[{"source_application_id":1,"application_id":1,"application_code":"APP1","is_administrator":"true"}],"environments":[{"environment_id":1,"environment_code":"1","is_administrator":"true"}]}}]

This depends where you look at the string...

In SSMS a string is marked with single quotes. The double quote can exist within a string without problems:

DECLARE @SomeString = 'This can include "double quotes" but you have to double ''single quote''';

In a C# application the double quote is the string marker. So the above example would look like this:

string SomeString = "This must escape "double quotes" but you can use 'single quote' without problems";

Within your IDE (is it VS?) you can look at the string as is or as you'd need to be used in code. Your example shows " at the beginning and at the end of your string. That is a clear hint, that this is the option as in code. You could use this string and place it into your code. The real string, which is used and processed will not contain escape characters.

Hint: Escape characters are only needed in human-readable formats, where there are characters with special meaning (a ; in a CSV, a < in HTML and so on)...

UPDATE Some more explanation

Escape characters are needed to place a string within a string. Somehow you have to mark the beginning and the end of the string, but there is nothing else you can use then some magic characters.

In order to use these characters within the embedded string you have to go one the following ways:

1. escaping (e.g. XML will replace & with & and JSON will replace a " with " as JSON uses the " to mark its labels) or


2. 
   Magic borders (e.g. a CDATA-section in XML, which allows to place unescaped characters as is: <![CDATA[forbidden characters &<> allowed here]]>)

Whatever you do, you must distinguish between the visible string in an editor or in a text-based container like XML or JSON and the value the application will pick out of this.

An example:

<root><a>this &amp; that</a></root> 

visible string: "this &amp; that"

real value: "this & that"