Is it possible to change the JSON key with JSON_MODIFY?

Using the data example shown here, 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}]')

Sample 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 result 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