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

CodePudding user response：

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

CodePudding user response：

You need to unpivot the JSON values into separate rows.

You can use another OPENJSON call, or if you know the names upfront you can do this using CROSS APPLY (VALUES

UPDATE WeeklySlots
SET slots = v.slots
FROM WeeklySlots ws
JOIN OPENJSON (@payload)
  WITH 
  (
    dow       int,
    T19_00    int,
    T19_30    int,
    T20_00    int,
    T20_30    int
  ) p
  CROSS APPLY (VALUES
      ('T19_00', p.T19_00),
      ('T19_00', p.T19_00),
      ('T19_00', p.T19_00),
      ('T19_00', p.T19_00)
  ) v(slotCode, slots)
  ON p.dow = ws.dow AND v.slotCode = ws.slotCode
;

db<>fiddle