JSON_MODIFY() embeds escape characters

I have a JSON Array that I load from a table, and would like to update each array element by adding to each, an array object. When I embed the new Array, the embedded object has added to it, an escape character for each double quote.

If possible, I'd like to rewrite the SQL to prevent the added "\" and avoid using the REPLACE() function

What am I doing incorrectly?

Here is my SQL:

DECLARE @TradeDate   Date        = GETDATE();
DECLARE @Disposition VARCHAR(20) = 'Day-Ahead';

DECLARE @JSONData  NVARCHAR(MAX) = '[{"Name":"A Confederacy Of Dunces", "BookOrder": 1}]';

DECLARE @TradeDateParm   VARCHAR(100)=CONCAT('"TradeDate":"'  , @TradeDate  , '"');
DECLARE @DispositionParm VARCHAR(100)=CONCAT('"Disposition":"', @Disposition, '"');

DECLARE @Parms VARCHAR(200)  = CONCAT('{',@TradeDateParm,',',@DispositionParm,'}');

PRINT @@VERSION
PRINT CONCAT('@TradeDateParm:   ',@TradeDateParm  );
PRINT CONCAT('@DispositionParm: ',@DispositionParm);
PRINT CONCAT('@Parms:           ',@Parms          );
PRINT ''
PRINT CONCAT('Original JSON: ',@JSONData);
PRINT CONCAT('Modified JSON: ',JSON_MODIFY(@JSONData, '$[0].Params', @Parms));

and the output is below.

Microsoft SQL Server 2019 (RTM-CU25-GDR) (KB5036335) - 15.0.4360.2 (X64) 

@TradeDateParm:   "TradeDate":"2024-05-23"
@DispositionParm: "Disposition":"Day-Ahead"
@Parms:           {"TradeDate":"2024-05-23","Disposition":"Day-Ahead"}
 
Original JSON: [{"Name":"A Confederacy Of Dunces", "BookOrder": 1}]
Modified JSON: [{"Name":"A Confederacy Of Dunces", "BookOrder": 1,"Params":"{\"TradeDate\":\"2024-05-23\",\"Disposition\":\"Day-Ahead\"}"}]

Solution

JSON_MODIFY escapes all strings, unless they come from known good sources of JSON. These include only JSON_QUERY, OPENJSON using AS JSON, and FOR JSON when not using WITHOUT_ARRAY_WRAPPER. And it must be directly in the same statement and scope, not in a different statement stored in a variable or table.

To force it to assume good JSON, just put it in a JSON_QUERY with no path.

PRINT CONCAT('Modified JSON: ',JSON_MODIFY(@JSONData, '$[0].Params', JSON_QUERY(@Parms)));

However, I'd advise you not to construct JSON yourself anyway, it's a minefield. For a start, you have the wrong data type (should be nvarchar(max)) and you are also not escaping correctly.

Just use nested FOR JSON queries.

DECLARE @TradeDate   Date        = GETDATE();
DECLARE @Disposition VARCHAR(20) = 'Day-Ahead';

DECLARE @JSONData  NVARCHAR(MAX) = '[{"Name":"A Confederacy Of Dunces", "BookOrder": 1}]';

DECLARE @Parms NVARCHAR(MAX) = (
    SELECT
      TradeDate = @TradeDate,
      Disposition = @Disposition
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES
);

PRINT @@VERSION
PRINT CONCAT('@TradeDate:   ',@TradeDate  );
PRINT CONCAT('@Disposition: ',@Disposition);
PRINT CONCAT('@Parms:           ',@Parms          );
PRINT ''
PRINT CONCAT('Original JSON: ',@JSONData);
PRINT CONCAT('Modified JSON: ',JSON_MODIFY(@JSONData, '$[0].Params', JSON_QUERY(@Parms)));