How to retain new line character in JSON_VALUE SQL server

Question

How to retain new line character in JSON_VALUE output in SQL server.

DECLARE @jsonString NVARCHAR(MAX) ='{"name":"jonh","address":"100 st \n city ,country"}'
SELECT JSON_VALUE(@jsonString,'$.address')

Output is "100 st city ,country"

but I want output to be "100 st \n city ,country"

How to retain new line character.

Answer 1

Escape the \ in the json:

DECLARE @jsonString NVARCHAR(MAX) ='{"name":"jonh","address":"100 st \\n city ,country"}'
SELECT JSON_VALUE(@jsonString,'$.address')

Answer 2

It is preserved, just SSMS converts it to a space when displaying in a table. You can check it by converting to varbinary or by changing SSMS output to text:

DECLARE @jsonString NVARCHAR(MAX) ='{"name":"jonh","address":"\n100 st \n city ,country"}'

SELECT JSON_VALUE(@jsonString,'$.address'),cast(JSON_VALUE(@jsonString,'$.address') as varbinary(100))

You can use solution from SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques? to retain them in SSMS.

Answer 3

Another possible approach, using STRING_ESCAPE() with 'json' as value for the type parameter:

Statement:

DECLARE @jsonString NVARCHAR(MAX) = N'{"name":"jonh","address":"100 st \n city ,country"}'
SELECT STRING_ESCAPE(JSON_VALUE(JSON_QUERY(@jsonString), '$.address'), 'json')

Result:

100 st \n city ,country