SQL Server 2019 - Update Table by JSON Array of object

Question

I have a table like this:

CREATE TABLE WeeklySlots
    ([dow] int, [slots] int, [SlotCode] varchar(6))
;
    
INSERT INTO WeeklySlots
    ([dow], [slots], [slotCode])
VALUES
    (1, 0, 'T19_00'),
    (2, 20, 'T19_00'),
    (3, 20, 'T19_00'),
    (4, 20, 'T19_00'),
    (5, 20, 'T19_00'),
    (6, 20, 'T19_00'),
    (7, 20, 'T19_00'),
    (1, 0, 'T19_30'),
    (2, 20, 'T19_30'),
    (3, 20, 'T19_30'),
    (4, 20, 'T19_30'),
    (5, 10, 'T19_30'),
    (6, 10, 'T19_30'),
    (7, 20, 'T19_30'),
    (1, 0, 'T20_00'),
    (2, 20, 'T20_00'),
    (3, 20, 'T20_00'),
    (4, 20, 'T20_00'),
    (5, 10, 'T20_00'),
    (6, 10, 'T20_00'),
    (7, 20, 'T20_00'),
    (1, 0, 'T20_30'),
    (2, 20, 'T20_30'),
    (3, 20, 'T20_30'),
    (4, 20, 'T20_30'),
    (5, 20, 'T20_30'),
    (6, 20, 'T20_30'),
    (7, 20, 'T20_30');

then I need to update it with a json payload (created with a pivot function from the above table)

@payload nvarchar(max)=N'[
   {
      "dow": 1,
      "T19_00": 10,
      "T19_30": 10,
      "T20_00": 10,
      "T20_30": 10
   },
   {
      "dow": 2,
      "T19_00": 20,
      "T19_30": 20,
      "T20_00": 20,
      "T20_30": 20
   },
   {
      "dow": 3,
      "T19_00": 20,
      "T19_30": 20,
      "T20_00": 20,
      "T20_30": 20
   },
   {
      "dow": 4,
      "T19_00": 20,
      "T19_30": 20,
      "T20_00": 20,
      "T20_30": 20
   },
   {
      "dow": 5,
      "T19_00": 20,
      "T19_30": 20,
      "T20_00": 20,
      "T20_30": 30
   },
   {
      "dow": 6,
      "T19_00": 20,
      "T19_30": 20,
      "T20_00": 20,
      "T20_30": 30
   },
   {
      "dow": 7,
      "T19_00": 20,
      "T19_30": 20,
      "T20_00": 20,
      "T20_30": 20
   }
]'

I understand how to parse it:

SELECT
    p2.*
from OPENJSON (@payload)
    WITH    (
                current_dow nvarchar(max) '$' as JSON
            )
CROSS APPLY OPENJSON(current_dow)
    WITH 
    (
        [dow]       nvarchar(64)    '$.dow',
        [T19_00]    nvarchar(64)    '$.T19_00',
        [T19_30]    nvarchar(64)    '$.T19_30',
        [T20_00]    nvarchar(64)    '$.T20_00',
        [T20_30]    nvarchar(64)    '$.T20_30'  
    ) p2

but how can I update the WeeklySlots Table?
Do I need a recursive function?
Can suggest the right path to solve it?

PS: I made this sqlFiddle to better explain the problem

Thanks

Answer 1

You may try to parse the input JSON differently. Note, that when you use OPENJSON() with the default schema, the key column is an nvarchar(4000) value with a BIN2 collation.

SELECT 
   CONVERT(int, JSON_VALUE(j1.[value], '$.dow')) AS dow, 
   CONVERT(int, j2.[value]) AS slots, 
   CONVERT(varchar(6), j2.[key] COLLATE DATABASE_DEFAULT) AS SlotCode
FROM OPENJSON(@payload) j1
CROSS APPLY OPENJSON(j1.[value]) j2
WHERE j2.[key] <> N'dow'

The UPDATE statement is:

UPDATE ws
SET ws.slots = j.slots
FROM WeeklySlots ws
INNER JOIN (
   SELECT 
      CONVERT(int, JSON_VALUE(j1.[value], '$.dow')) AS dow, 
      CONVERT(int, j2.[value]) AS slots, 
      CONVERT(varchar(6), j2.[key] COLLATE DATABASE_DEFAULT) AS SlotCode
   FROM OPENJSON(@payload) j1
   CROSS APPLY OPENJSON(j1.[value]) j2
   WHERE j2.[key] <> N'dow'
) j ON ws.dow = j.dow AND ws.SlotCode = j.SlotCode