Line feed in OPENJSON

Question

I got a problem with OPENJSON.

DECLARE @X AS VARCHAR(1000) = CONCAT('[{"KEY":1, "VALUE": "A', CHAR(10) ,'B"}]')

SELECT
     *    
FROM
    OPENJSON(@X)  
    WITH (
        [KEY] INT 'strict $.KEY',
        [VALUE] VARCHAR(1000) 'strict $.VALUE'
    )

This doesn't work because I used a line feed (char(10)), how can I fix this? Carriage return (char(13)) doesn't work.

score 1 · Answer 1 · answered Jul 29 '20 at 01:51

1

Char(10) and char(13) are special characters in JSON and must be escaped. You can see this question here on how-to How to escape special characters in building a JSON string?

To fix your current issue you can do this

....
OPENJSON(replace(@X, char(10), '\n' ) )  
....

answered Jul 29 '20 at 01:51

K4M

1,030
3
11

also in your example case, <....CHAR(10) ,'B"]')> should read as <....CHAR(10) ,'B"}]')>. I mean it is missing the closing curly bracket – K4M Jul 29 '20 at 01:53

Zhorov · Answer 2 · 2020-07-29T12:13:02.403

You need to escape the CHAR(10) special character and you may use STRING_ESCAPE() function with 'json' as second parameter (currently the only possible option) to escape all special characters in the input text:

Statement:

DECLARE @X AS VARCHAR(1000) = CONCAT(
  '[{"KEY":1, "VALUE": "A', 
  STRING_ESCAPE(CHAR(10), 'json'),
  'B"}]'
)

SELECT *    
FROM OPENJSON(@X) WITH (
   [KEY] INT 'strict $.KEY',
   [VALUE] VARCHAR(1000) 'strict $.VALUE'
)

Result:

KEY VALUE
1   A
    B

Line feed in OPENJSON

2 Answers2