SQL Server: Update table based on JSON

Question

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:)

Answer 1

1) once you change the json into a select statement, you can iterate over that using cursor.

2) you can treat json select statement as a table. That said, you can do insert, update, delete operations exactly as you do with two tables. For updated case you can use code like this:

With Json_data as 
( SELECT .......
  FROM OPENJSON(@json)
  WITH (
    id varchar(200),
    ....
  )

update S set ....
from dbo.sensors as S 
inner join Json_data as JD on JD.id = S.id