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

Zhorov · Accepted Answer · 2022-10-14T06:43:08.407

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

Hmm.. a simple join... thanks also for the parsing.. actually in my version there was an "empty" OPENJSON, but did not know how to avoid it. I try and let you know, thanks for the moment! — Joe, Oct 14 '22 at 09:04

score 0 · Answer 2 · answered Oct 14 '22 at 11:43

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

Yes, was just missing the `UPDATE`, Thanks! Actually I know the names of the fields, but Zhorov solution does not require to hardcode them, therefore is more flexible. — Joe, Oct 14 '22 at 19:25

SQL Server 2019 - Update Table by JSON Array of object

2 Answers2