Update table using JSON in SQL

Question

Is there a way to update a table using a JSON field in SQL.

{
    "RelationshipType" : [ 
        {
            "ID" : 1,
            "FromID" : 70,
            "ToID" : 12
        },
        {
            "ID" : 3,
            "FromID" : 80,
            "ToID" : 1
        }
    ]
}

Table structure

ID    |    FromID    |    ToID    |
1     |     10       |     12     |
2     |     42       |     17     |
3     |    100       |     1      |

If I update the table using the above mentioned JSON file in SQL server the output should be changed as below by matching the FromID to ID.

ID    |    FromID    |    ToID    |
1     |     70       |     12     |
2     |     42       |     17     |
3     |     80       |     1      |

In order to achieve this currently I am using the below query.

DECLARE @Relationship AS TABLE(FromID INT,ToID INT,ID INT)
INSERT INTO @Relationship (FromID,ToID,ID)
SELECT FromID, ToID, ID
FROM OPENJSON(@RelationshipType)
WITH (FromID INT, ToID INT, ID INT)

I am iterating the @Relationship table and updating the data. Is there a proper way to do it using JSON operations without iterating the temp table.

Answer 1

First, you need to specify the path '$.RelationshipType' in OPENJSON() to make it extract the data correctly.

Second, it is possible to JOIN OPENJSON() results just like any table, also using UPDATE, as follows:

UPDATE U
SET    FromID = J.FromID
FROM   YourTable AS U
JOIN   OPENJSON(@RelationshipType, '$.RelationshipType')
       WITH (ID INT, FromID INT, ToID INT) J
       ON J.ID = U.ID

See it in action:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c37ef5a175c0f19e0f990dc6a219ce26

Answer 2

Your OPENJSON is missing the path... Isn't it?

DECLARE @relationshipType NVARCHAR(MAX)=
N'{
    "RelationshipType" : [ 
        {
            "ID" : 1,
            "FromID" : 70,
            "ToID" : 12
        },
        {
            "ID" : 3,
            "FromID" : 80,
            "ToID" : 1
        }
    ]
}';

--This is to mock-up your existing data

DECLARE @ExistingTable AS TABLE(ID INT,FromID INT,ToID INT)
INSERT INTO @ExistingTable VALUES(1,10,12),(2,42,17),(3,100,1);

--The query will use two CTEs:
--1) Get the JSON data as list
--2) Bind the new data to the corresponding rows

WITH newData AS
(
    SELECT *
    FROM OPENJSON(@relationshipType,'$.RelationshipType')
    WITH (FromID INT, ToID INT, ID INT)
)
,updatableCTE AS
(
    SELECT et.*
          ,nd.FromID AS newFromID
          ,nd.ToID AS newToID
    FROM @ExistingTable et
    INNER JOIN newData nd ON et.ID=nd.ID
)
UPDATE updatableCTE SET FromID=newFromID
                       ,ToID=newToID;

--check the result

SELECT ID,FromID,ToID FROM @ExistingTable;

The result

ID  FromID    ToID
1     70        12
2     42        17
3     80         1