Getting Unescaped JSON from SQL

Question

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.

Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Nov 13 '18 at 17:15
@marc_s question about editing: why remove horizontal rules and header bolds? It makes the document very difficult to read now. Thanks for the mention on the sp_ prefix, I'll make sure to avoid doing that in the future. — expenguin, Nov 13 '18 at 17:21
SQL server isn't adding the escape characters, .net is. if you're assigning the json response to a string in .net, .net is adding the escape characters. You'd have to deal with that on the .net side. Quick search https://stackoverflow.com/questions/16692371/replacing-escape-characters-from-json — Tim Mylott, Nov 13 '18 at 21:15
`SET @JSON = (SELECT '{"Field":"Value"}' AS myJson FOR JSON PATH) SELECT JSON_QUERY(@JSON) AS 'JsonResponse' FOR JSON PATH` puts JSON inside and string value inside JSON, so SSMS escapes it. — Josh Gust, Aug 11 '21 at 00:40

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

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:

escaping (e.g. XML will replace & with & and JSON will replace a " with \" as JSON uses the " to mark its labels) or
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"

Hey, thanks for the reply. To add to this, we actually use the JSON response and create a token in ADFS. Using JWT (https://jwt.io/) to check the token, the escape characters are included within when it's decoded. I was using .NET in order to view the incoming object and noticed the same behavior. I was hoping to have the JSON in the token be escapeless, but from what you're telling me, I don't know if that's possible since the escape characters are dependent on the receiving application. How can I view the original string without escapes? Is it just a matter of typing my variable? — expenguin, Nov 14 '18 at 20:48
@expenguin I do not know, if you really got the ghist... See my update — Shnugo, Nov 16 '18 at 10:31

Getting Unescaped JSON from SQL

1 Answers1

UPDATE Some more explanation