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-14 06:43:08Z

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

edited Oct 14, 2022 at 6:43

answered Oct 14, 2022 at 6:09

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Over a year ago

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!

Charlieface · Accepted Answer · 2022-10-14 11:43:28Z

0

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

answered Oct 14, 2022 at 11:43

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

1 Comment

Joe Over a year ago

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.

Collectives™ on Stack Overflow

SQL Server 2019 - Update Table by JSON Array of object

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related