CODEWITHSUNDEEP
sqljsonsql-servert-sql
expenguin
expenguin

Reputation: 1124

Getting Unescaped JSON from SQL

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.

Upvotes: 7

Views: 10528

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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 &amp; 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"

Upvotes: 2

Related Questions