Skip to main content

Return to Question

added 232 characters in body; edited title
Source Link
marc_s
marc_s
  • 747.8k
  • 180
  • 1.4k
  • 1.5k

OpenJson to parse and partially update a json column fieldcolumn

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 JsonConfigJsonConfig column has this data

I'm recievingreceiving a json document like this  :

thisThis is what I have done so far but don't know how to extract each resource[]resource[] item to insert it's fields to the item [].status[].status

ALTER PROCEDURE sp_process_device_stat
    (@json nvarchar(MAX))
AS BEGIN
BEGIN
Update    UPDATE device
SET
    [type]=SET [type] = Item.[type],
    [version]=    [version] = Item.ver,  
    [ip]=    [ip] = Item.[ip], 
    [uptime]=    [uptime] = Item.uptime, 
    [memory]=    [memory] = Item.mem, 
    [rssi]=    [rssi] = Item.rssi, 
        [jsonStatus] = Item.resources,
        [DateLastSeen] = getdateGETDATE()
    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=deviceserial = device.serial;
 
END

OpenJson to parse and partially update a json column field

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

I'm recieving a json document like this  :

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

OpenJson to parse and partially update a json column column

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

I'm receiving a json document like this:

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
Source Link
Marc
Marc
  • 2.1k
  • 4
  • 16
  • 31

OpenJson to parse and partially update a json column field

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 recieving 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