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

Answer 2

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.

Answer 3

;With Json_data as 
( SELECT 
Evaluation_IDNO,            
Rating_IDNO,                
Notes_TEXT,             
NextSteps_TEXT,         
EvaluationCategory_CODE,
EvalType_ID            
  FROM OPENJSON(@As_EvaluationCategory_Json) WITH
            (   
                Evaluation_IDNO         INT             N'$.matrixId',                  
                Rating_IDNO             VARCHAR(150)    N'$.ratingValue',                       
                Notes_TEXT              VARCHAR(MAX)    N'$.notesText',
                NextSteps_TEXT          VARCHAR(MAX)    N'$.nextStepsText',
                EvaluationCategory_CODE VARCHAR(50)     N'$.ratingData',
                EvalType_ID             VARCHAR(4)     N'$.evalTypeId'
            )
            AS EvaluationCategories
        )

UPDATE EvaluationRatings_T1 SET
UserCreatedBy_ID=@As_SignedOnWorker_ID,
User_ID=@Ls_User_Id,
WorkspaceCreatedBy_ID=@Ls_WorkspaceCreatedBy_Id,
BeginValidity_DTTM=@Ls_Evaluation_DTTM,
EndValidity_DTTM=@Ld_HighValidity_DTTM,
TransactionEvenSeq_NUMB=@An_TransactionEventSeq_NUMB,
Update_DTTM=@Ld_BeginValiditiy_DTTM,
WorkspaceUpdatedBy_ID=@Ls_WorkspaceUpdatedBy_ID,
Evaluation_IDNO=c1.Evaluation_IDNO,
Rating_IDNO=c1.Rating_IDNO,
Notes_TEXT=c1.Notes_TEXT,
NextSteps_TEXT=c1.NextSteps_TEXT,
EvaluationCategory_CODE=c1.EvaluationCategory_CODE,
EvalType_ID=c1.EvalType_ID 
FROM Json_data c1
inner JOIN EvaluationRatings_T1 e1 on e1.Evaluation_IDNO=c1.Evaluation_IDNO
WHERE e1.Evaluation_IDNO=@AS_Evaluation_IDNO;