Use of JSON_VALUE to return a JSON value containing \n as the newline character

Question

Consider the following T-SQL in Microsoft SQL Server:

SELECT JSON_VALUE ('{"Value": "Normal Text 304\n1212\n1212\n121\na"}', '$.Value')

When this is executed, \n is converted to ASCII 10 (line feed) in the data, and it returns the following:

Normal Text 304 1212 1212 121 a

However, I need the JSON value to be returned as follows so the value can be injected into another JSON structure.

Normal Text 304\n1212\n1212\n121\na

For example:

SELECT
    ISJSON ('{"New Value": "' +  JSON_VALUE ('{"Value": "Normal Text 304\n1212\n1212\n121\na"}', '$.Value') +'"}')

This returns 0 as the JSON is not valid as it contains ASCII code 10.

I would appreciate any advice on how to get a string value in JSON format from a JSON structure to paste into another JSON structure?

I could do a conversion from ASCII code 10 back to \n but that is not an ideal solution.

Answer 1

If you use SQL Server 2017+, STRING_ESCAPE() is an option:

SELECT STRING_ESCAPE(
   JSON_VALUE('{"Value": "Normal Text 304\n1212\n1212\n121\na"}', '$.Value'), 
   'json'
) As EscapedJson

Result:

EscapedJson
-----------------------------------
Normal Text 304\n1212\n1212\n121\na

You may modify an existing JSON content using JSON_MODIFY(). In this situation, as is explained in the documentation, JSON_MODIFY() ...escapes all special characters in the new value if the type of the value is NVARCHAR or VARCHAR.

SELECT JSON_MODIFY(
   '{"Key": "Normal Text"}',
   '$."New Value"',
   JSON_VALUE('{"Value": "Normal Text 304\n1212\n1212\n121\na"}', '$.Value')
) AS ModifiedJson

Result:

ModifiedJson
------------------------------------------------------------------------
{"Key": "Normal Text","New Value":"Normal Text 304\n1212\n1212\n121\na"}