Tukukkk

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.

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

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