json sql-server database t-sql open-json

OpenJson to parse and partially update a json column column

I have this table:

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
)

The JsonConfig column has this data

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": {}
  }
]';

I'm receiving a json document like this:

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

I want to update/replace "status" with whatever information is contained in the new document at the same array position (the field names contained in status are not known in advance), for example :

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

becomes :

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

This is what I have done so far but don't know how to extract each resource[] item to insert it's fields to the item [].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

Solution

You can try something along this:

--declare your two json strings

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

--the query will use a cursor (because JSON_MODIFY() allows for a single change only)

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.