Skip to content
Advertisement

SQL Server: Update table based on JSON

I am trying to update rows in table based on JSON I have. JSON has the following structure:

"sensors": [
{
  "id": "5afd7160f16819f11814f6e2",
  "num": 0,
  "name": "AC01",
  "enabled": true,
  "unit": "Volt AC Phase 1",
  "desc": "NAMsdafE",
  "lt_disaster": 1,
  "gt_disaster": 1,
  "lt_high": 1,
  "gt_high": 1,
  "lt_average": 1,
  "gt_average": 1
},...

Table dbo.sensors has same structure + few more columns. To insert such JSON object, not array, into table, I would do it this way:

INSERT INTO dbo.sensors (.......)
  SELECT .......
  FROM OPENJSON(@json)
  WITH (
    id varchar(200),
    ....
  );

So I have 2 questions: how to iterate over each element in JSON array and update each row with the same id. Any help would be appreciated:)

Advertisement

Answer

First, read documentation OPENJSON. This feature is available starting version 2016.
Next, apply new knowledge.

--truncated for shortness
--note: wrap JSON string in curly brackets {}
declare @json nvarchar(max)='{"sensors":[
{
  "id": "5afd7160f16819f11814f6e2",
  "num": 0,
  "name": "AC01",
  "more": "unused"
},
{  "id": "5afd7160f16819f11814f6e3",
  "num": 0,
  "name": "AC02"
}]}
'

--insert... 
select * from 
openjson(@json,'$.sensors') --note the "path" argument here
with(
id varchar(200),
num int,
name varchar(10)
) json --alias sometimes required.

You can use result (rowset) as it is a table.

Advertisement