SQLServer2019-按物件的JSON陣列更新表

我有一張這樣的桌子：

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');

然后我需要使用json有效負載更新它（使用上表中的資料透視函式創建）

@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
   }
]'

我了解如何決議它：

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

但是如何更新 WeeklySlots 表？
我需要遞回函式嗎？
可以提出正確的解決方法嗎？

PS：我做了這個sqlFiddle來更好地解釋問題

謝謝

uj5u.com熱心網友回復：

您可以嘗試以JSON不同的方式決議輸入。請注意，當您使用OPENJSON()默認架構時，該key列是一個nvarchar(4000)帶有BIN2排序規則的值。

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'

UPDATE宣告是：

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

uj5u.com熱心網友回復：

您需要將 JSON 值還原為單獨的行。

您可以使用另一個OPENJSON電話，或者如果您預先知道名稱，您可以使用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<>小提琴

標籤：sqljsonsql服务器tsql开放的json

上一篇：顯示表中缺少值的帳戶

下一篇：設定可從外部讀取的會話屬性