Update table using JSON in SQL

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

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

Advertisement

Answer