OpenJson決議和部分更新json列-有解無憂

我有這張桌子：

CREATE TABLE [dbo].[Device]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [DeviceStatus] [int] NOT NULL,
    [Type] [nvarchar](64) NOT NULL,
    [Serial] [nvarchar](64) NOT NULL,
    [Group] [nvarchar](max) NULL,
    [Name] [nvarchar](max) NULL,
    [IP] [nvarchar](max) NULL,
    [Description] [nvarchar](max) NULL,
    [JsonConfig] [nvarchar](max) NULL,
    [JsonStatus] [nvarchar](max) NULL,
    [RSSI] [int] NOT NULL,
    [DateCreated] [datetime2](7) NOT NULL,
    [DateUpdated] [datetime2](7) NOT NULL,
    [DateLastSeen] [datetime2](7) NOT NULL,
    [BatteryVoltage] [int] NOT NULL,
    [IsBatteryPowered] [bit] NOT NULL,
    [Uptime] [int] NOT NULL,
    [Memory] [int] NOT NULL,
    [Version] [nvarchar](max) NULL
)

該JsonConfig列有此資料

declare @json nvarchar(max)
set @json = '
[
  {
    "item": 0,
    "type": "switch",
    "name": "item 1",
    "status": {}
  },
  {
    "item": 1,
    "type": "switch",
    "name": "item 2",
    "status": {}
  },
  {
    "item": 2,
    "type": "switch",
    "name": "item 3",
    "status": {}
  },
  {
    "item": 3,
    "type": "switch",
    "name": "item 4",
    "status": {}
  },
  {
    "item": 4,
    "type": "switch",
    "name": "item 5",
    "status": {}
  },
  {
    "item": 5,
    "type": "switch",
    "name": "item 6",
    "status": {}
  },
  {
    "item": 6,
    "type": "switch",
    "name": "item 7",
    "status": {}
  },
  {
    "item": 7,
    "type": "switch",
    "name": "item 8",
    "status": {}
  }
]';

我收到這樣的 json 檔案：

declare @jsonStat nvarchar(max)
set @jsonStat = '{
  "serial": "locker-7C9EBD6074F8",
  "type": "locker",
  "ver": "0.1",
  "ip": "192.168.1.133",
  "uptime": 79,
  "mem": 210888,
  "rssi": -36,
  "resources": [
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false,
      "valueA" : 1
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false,
      "valueB" : "test B"
    },
    {
      "busy": true,
      "enabled": true,
      "duration": 5,
      "timer": true
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    }
  ]
}';

我想用新檔案中包含在同一陣列位置的任何資訊來更新/替換“狀態”（狀態中包含的欄位名稱事先不知道），例如：

   {
    "item": 0,
    "type": "switch",
    "name": "item 1",
    "status": {}
  },

變成：

   {
    "item": 0,
    "type": "switch",
    "name": "item 1",
    "status": {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false,
      "valueA" : 1
    }
  },

這是我到目前為止所做的，但不知道如何提取每個resource[]專案以將其欄位插入專案[].status

ALTER PROCEDURE sp_process_device_stat
    (@json nvarchar(MAX))
AS 
BEGIN
    UPDATE device
    SET [type] = Item.[type],
        [version] = Item.ver,  
        [ip] = Item.[ip], 
        [uptime] = Item.uptime, 
        [memory] = Item.mem, 
        [rssi] = Item.rssi, 
        [jsonStatus] = Item.resources,
        [DateLastSeen] = GETDATE()
    FROM 
        OPENJSON(@json)
        WITH
            ([serial] nvarchar(100),
             [type] nvarchar(100),
             [ver] nvarchar(100),
             [ip] nvarchar(100),
             [uptime] nvarchar(100),
             [mem] nvarchar(100),
             [rssi] nvarchar(100),
             [resources] nvarchar(max) as JSON) as Item
    JOIN 
        [Device] device ON Item.serial = device.serial;
END

uj5u.com熱心網友回復：

你可以試試這個：

--宣告你的兩個json字串

declare @json nvarchar(max)
set @json = '
[
  {
    "item": 0,
    "type": "switch",
    "name": "item 1",
    "status": {}
  },
  {
    "item": 1,
    "type": "switch",
    "name": "item 2",
    "status": {}
  },
  {
    "item": 2,
    "type": "switch",
    "name": "item 3",
    "status": {}
  },
  {
    "item": 3,
    "type": "switch",
    "name": "item 4",
    "status": {}
  },
  {
    "item": 4,
    "type": "switch",
    "name": "item 5",
    "status": {}
  },
  {
    "item": 5,
    "type": "switch",
    "name": "item 6",
    "status": {}
  },
  {
    "item": 6,
    "type": "switch",
    "name": "item 7",
    "status": {}
  },
  {
    "item": 7,
    "type": "switch",
    "name": "item 8",
    "status": {}
  }
]';

declare @jsonStat nvarchar(max)
set @jsonStat = '{
  "serial": "locker-7C9EBD6074F8",
  "type": "locker",
  "ver": "0.1",
  "ip": "192.168.1.133",
  "uptime": 79,
  "mem": 210888,
  "rssi": -36,
  "resources": [
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false,
      "valueA" : 1
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false,
      "valueB" : "test B"
    },
    {
      "busy": true,
      "enabled": true,
      "duration": 5,
      "timer": true
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    },
    {
      "busy": false,
      "enabled": true,
      "duration": 0,
      "timer": false
    }
  ]
}';

--查詢將使用游標（因為JSON_MODIFY()只允許進行一次更改）

DECLARE @insP INT;
DECLARE @stat NVARCHAR(MAX);

DECLARE cur CURSOR FOR SELECT B.[key] AS InsertPosition
                             ,B.[value] AS statusToInsert
                       FROM        OPENJSON(@jsonStat) WITH([resources] NVARCHAR(MAX) AS JSON) A
                       OUTER APPLY OPENJSON(A.resources) B;
OPEN CUR;
FETCH NEXT FROM cur INTO @insP,@stat;
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT CONCAT(@insP ,' ->', @stat);
    SET @json = JSON_MODIFY(@json,CONCAT(N'$[',@insP,'].status'),JSON_QUERY(@stat));
    FETCH NEXT FROM cur INTO @insP,@stat;
END
CLOSE CUR;
DEALLOCATE CUR;

PRINT @json

The idea in short:

We read resources from @jsonStat using a WITH clause in order to tell the engine that this fragment is a JSON portion itself.
We use another APPLY OPENJSON retrieving the item's position and value.
With this query we travers down using a cursor
Within the cursor we can use the position and content to execute JSON_MODIFY() once for each.
The final PRINT shows the success.

Hint: If you know your JSON's fields in advance (and expect no changes to this) you migth deconstruct it to a table and reconstruct the JSON in one go. For this you'd use a WITH-clause to geht all your fields in tabular result and build the JSON using FOR JSON PATH at the end.

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422522.html

標籤：

上一篇：SQL：登錄'abc'擁有一個或多個資料庫......即使我剛剛洗掉了這些角色

下一篇：我能知道這個SQL查詢的問題嗎