STRING_ESCAPE JSON Text: Escaping Is Not Serialization

STRING_ESCAPE JSON text handles special characters inside a string value. I still need a complete document when the next component expects properties, brackets and valid JSON structure.

Keep the two outputs separate

A message can contain quotation marks, a path separator or a line break. Those characters need a safe representation inside JSON string syntax. Escaping handles that representation. It doesn’t invent the surrounding property name or object structure for me.

I call STRING_ESCAPE with the json escaping type. Its result is nvarchar(max). The function handles the relevant special and control characters. SQL Server introduced it in the 2016 release.

I prefer to identify the receiver’s contract before choosing the expression. Does it need escaped string contents, or an entire JSON object? Sending the first as the second leaves structure missing. Treating both outputs as interchangeable makes debugging harder.

Compare original values with serialized objects

The example uses three independent text values. One contains quoted text, another contains a backslash, and the last contains a newline. The original values remain beside their escaped representations. That gives each changed character a visible source.

FOR JSON PATH produces the complete object in a separate column. The query supplies the original value directly to that serializer. It doesn’t pass the already escaped value. That keeps one serialization step responsible for the JSON representation.

Each object contains an integer id and a text property. WITHOUT_ARRAY_WRAPPER is used for the one-row correlated subquery. The object is then checked with ISJSON. This narrowly defined single-row shape avoids assuming the option fixes arbitrary multirow output.

WITH Inputs AS
(
    SELECT Id, CAST(RawText AS nvarchar(100)) AS RawText
    FROM (VALUES
        (1, N'A "quote"'),
        (2, N'C:\temp'),
        (3, N'line' + NCHAR(10) + N'two')
    ) AS v(Id, RawText)
)
SELECT i.Id, i.RawText,
       STRING_ESCAPE(i.RawText, 'json') AS EscapedText,
       j.DocumentText, ISJSON(j.DocumentText) AS DocumentIsJson
FROM Inputs AS i
CROSS APPLY
(
    SELECT (SELECT i.Id AS id, i.RawText AS text
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS DocumentText
) AS j
ORDER BY i.Id;

Do not escape the escape

Suppose the original value contains a quotation mark. STRING_ESCAPE introduces a backslash before that mark. If another serializer receives that escaped result as ordinary text, the backslash becomes part of the value. It needs its own escaping again.

The resulting document can remain valid JSON while carrying the wrong text. Valid syntax isn’t proof of the intended value. I’d compare a parsed text property with the original input. A visual count of backslashes is a poor substitute.

That distinction matters when logs show the JSON document’s textual representation. A backslash shown in a debugger can represent an escape sequence. Another backslash can represent an original character. Keep the original value available while investigating either case.

Let a serializer handle structure

FOR JSON serializes a query result. PATH mode gives control over the resulting object structure. Its aliases identify the properties in this example. The escaping helper and serializer therefore solve separate parts of the output problem.

I can defend hand-built JSON for a carefully bounded fragment. That decision also makes the builder responsible for every structural detail. Property separators, quoting and NULL treatment still need attention. A small current requirement doesn’t remove those obligations.

For a row-shaped result, I’d start with the available serializer. It keeps column values and structural punctuation in their proper roles. I would then check the receiver’s expected properties and types. Those checks belong alongside syntax validation.

Read the complete values

This demonstration reads literal inputs and creates no objects. The newline is produced with NCHAR rather than hidden inside the SQL source. A grid can display that character awkwardly. Check the full string values when comparing results, including the newline.

Keep the original text beside its escaped form and serialized document. Each DocumentIsJson flag is one for these three rows. Compare every complete string rather than only the validity flag. Truncated grid text cannot establish the full document.

Escape the string, serialize the object, and keep the two jobs apart.

Escaping is not a complete document, it is one string representation inside a serialization contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.