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