sqljsonsql-servert-sql

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


Solution

  • 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.