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"}"}]