Is it possible to change the JSON key with JSON_MODIFY?

Using the below data example, I wish to change the key (not the value) in column JsonData. One of the rows of data has the key of "Day", while the other row has the key of "DayOfWeek". I'd like to update the one row and change the key from "Day" to "DayOfWeek". The end result is this table should have JSON data where all the rows have keys "DayOfWeek" and "Time".

TABLE

CREATE TABLE TempTbl (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    JsonData nvarchar(max)
)

INSERT INTO TempTbl
   (JsonData)
VALUES 
   ('[{"Day":1,"Time":6},{"Day":2,"Time":11},{"Day":3,"Time":16}]'),
   ('[{"DayOfWeek":0,"Time":6},{"DayOfWeek":1,"Time":6}]')

EXAMPLE SELECT STATEMENT

SELECT 
    ID,
    JSonData,
    [Day],
    [DayOfWeek],
    [Time]
FROM TempTbl
CROSS APPLY OPENJSON(JsonData)
WITH (
    [Day] int '$.Day',
    [DayOfWeek] int '$.DayOfWeek',
    [Time] int '$.Time'
)
WHERE
    [Day] is NOT NULL

Expected Results for new data that lives inside TempTbl.JsonData column:

   ('[{"DayOfWeek":1,"Time":6},{"DayOfWeek":2,"Time":11},{"DayOfWeek":3,"Time":16}]'),
   ('[{"DayOfWeek":0,"Time":6},{"DayOfWeek":1,"Time":6}]')

I attempted JSON_Modify to no avail. Mainly because the JSON data to change is key elements inside an array, this is more difficult to resolve.

This seems close and may work with some modification: Update JSON using JSON_MODIFY

CodePudding user response：

Please try the following solution.

Because JSON value is a plain NVARCHAR(...) data type, a regular REPLACE() function call does the job.

SQL

DECLARE @tbl TABLE (ID int IDENTITY PRIMARY KEY, JsonData NVARCHAR(MAX));
INSERT INTO @tbl (JsonData) VALUES 
(N'[{"Day":1,"Time":6},{"Day":2,"Time":11},{"Day":3,"Time":16}]'),
(N'[{"DayOfWeek":0,"Time":6},{"DayOfWeek":1,"Time":6}]');

UPDATE @tbl
SET JsonData = REPLACE(JsonData, '"Day"', '"DayOfWeek"');

-- test
SELECT * FROM @tbl;

Output