JSON escape characters

Question

Post reply

JSON escape characters

Jo Pattyn

SSC-Dedicated

Points: 32350

May 31, 2024 at 9:29 am

#4425317

Hello,

I'm tasked to generate JSON files with the FOR JSON Clause of SQL Server

Is there an easy way to prevent the escape characters with FOR JSON PATH?

The columnames and dates are escaped with backslash according to https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-escapes-special-characters-and-control-characters-sql-server?view=sql-server-ver16

It should be possible with https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-ver16 somehow

WITH MyTests as
(
SELECT
'1' MyId
,'28/04/2024' MyDate
,'Habba' [My/Column]
)
,MyResults as
(
select 
'1' MyTestID
,'Allright' MyResult
)
select 
MyTests.MyId
,MyTests.MyDate
,MyTests.[My/Column]
,(
SELECT MyResults.MyResult
FROM MyResults
WHERE MyResults.MyTestID=MyTests.MyId
FOR JSON PATH
) Result
from MyTests
FOR JSON PATH

Results in

[
    {
        "MyId": "1",
        "MyDate": "28\/04\/2024",
        "My\/Column": "Habba",
        "Result": [
            {
                "MyResult": "Allright"
            }
        ]
    }
]

I woud like to output it as

[
    {
        "MyId": "1",
        "MyDate": "28/04/2024",
        "My/Column": "Habba",
        "Result": [
            {
                "MyResult": "Allright"
            }
        ]
    }
]

This topic was modified 7 months ago by Jo Pattyn. Reason: link to json query

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

I've just been trying a few things and couldn't make it work.

It seems that the T-SQL JSON functions deem any string containing a single / character as invalid and escape it accordingly.

Looking forward to seeing what others say. And whether they manage to avoid suggesting the use of REPLACE()!

PS, the second link in your post does not point to the right URL.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2174 More actions · Answer 2

Phil Parkin wrote:

It seems that the T-SQL JSON functions deem any string containing a single / character as invalid and escape it accordingly.

Yes removing the escape characters would invalidate the JSON afaik. It's like pregnant unescaped JSON is not valid JSON

https://www.json.org/json-en.html

According to the Standards doc:

A string is a sequence of zero or more Unicode characters, wrapped in double quotes, using backslash escapes.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

Jo Pattyn

SSC-Dedicated

Points: 32350

May 31, 2024 at 12:48 pm

#4425367

Thanks Steve, that explains it

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

but this being JSON why not use a date format that is more standard (ISO8601).

any software reading the file will most likely parse the value correctly, and you won't get those escape characters being added.

Phil Parkin SSC Guru Points: 246988 More actions · Answer 5

Interestingly, the desired output is however considered valid JSON, at least according to https://jsonlint.com/

JSON

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Steve Collins SSCrazy Points: 2174 More actions · Answer 6

Phil Parkin wrote:

Interestingly, the desired output is however considered valid JSON, at least according to https://jsonlint.com/

In the JSON Standards docs they call the forward slash symbol a 'solidus' and a backslash is a 'reverse solidus'. It is the reverse solidus which must be escaped. Forward slash solidus is optionally escaped, which is why both are valid. The link in my earlier post was to a summary of the doc. Here's the complete Internet Engineering Task Force (IETF) document on strings:

https://datatracker.ietf.org/doc/html/rfc8259#section-7

The representation of strings is similar to conventions used in the C
family of programming languages. A string begins and ends with
quotation marks. All Unicode characters may be placed within the
quotation marks, except for the characters that MUST be escaped:
quotation mark, reverse solidus, and the control characters (U+0000
through U+001F).
Any character may be escaped. If the character is in the Basic
Multilingual Plane (U+0000 through U+FFFF), then it may be
represented as a six-character sequence: a reverse solidus, followed
by the lowercase letter u, followed by four hexadecimal digits that
encode the character's code point. The hexadecimal letters A through
F can be uppercase or lowercase. So, for example, a string
containing only a single reverse solidus character may be represented
as "\u005C".
Alternatively, there are two-character sequence escape
representations of some popular characters. So, for example, a
string containing only a single reverse solidus character may be
represented more compactly as "\\".

[Edit]: removed the part about a 3rd representation. It seems I keep confusing solidus and reverse solidus

This reply was modified 7 months ago by Steve Collins. Reason: Removed mistake

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 7

Thanks

Steve for the Json references, Phil for the validation, Frederico for the date suggestion.

My workaround is storing it in an nvarchar(max) variable, replace the wanted character and write it back out.

Remarks:

Any "\/" would be originally "/" (have to test it yet)

The JSON is a drop-in replacement for an excelfile based solution presenting dates in DD/MM/YYYY. (minimizing code work)

DECLARE @MYJSON nvarchar(max);
WITH MyTests as
(
SELECT
'1' MyId
,'28/04/2024' MyDate
,'Habba' [My/Column]
)
,MyResults as
(
select 
'1' MyTestID
,'Allright' MyResult
)
SELECT @MYJSON=(
select 
MyTests.MyId
,MyTests.MyDate
,MyTests.[My/Column]
,(
SELECT MyResults.MyResult
FROM MyResults
WHERE MyResults.MyTestID=MyTests.MyId
FOR JSON PATH
) Result
from MyTests
FOR JSON PATH
) 
SELECT @MYJSON original
,REPLACE(@MYJSON,N'\/',N'/') replaced

Results in

original:

[{"MyId":"1","MyDate":"28\/04\/2024","My\/Column":"Habba","Result":[{"MyResult":"Allright"}]}]

replaced:

[{"MyId":"1","MyDate":"28/04/2024","My/Column":"Habba","Result":[{"MyResult":"Allright"}]}]

frederico_fonseca SSCoach Points: 16167 More actions · Answer 8

Jo Pattyn wrote:

Thanks
Steve for the Json references, Phil for the validation, Frederico for the date suggestion.
My workaround is storing it in an nvarchar(max) variable, replace the wanted character and write it back out.
Remarks:
Any "\/" would be originally "/" (have to test it yet)
The JSON is a drop-in replacement for an excelfile based solution presenting dates in DD/MM/YYYY. (minimizing code work)

excel would happily use the date on format yyyy-mm-dd