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.

@PeterB, if I get this "I am iterating the @Relationship table" correctly, the point is not the reading of the JSON, but the udate in one go... — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 19, 2018 at 10:09
@Shnugo. Exactly, Is there a solution in order to update the table directly without feeding the data to a Temp Table or a CTE? — Harsha W
– Harsha W, Commented Nov 19, 2018 at 10:10
@HarshaW, just check my answer... You can use UPDATE with a JOIN, or - my prefered approach - an updatable CTE. At least for me this is better to read... — Gottfried Lesigang
– Gottfried Lesigang, Commented Nov 19, 2018 at 10:20

Peter B · Accepted Answer · 2018-11-19 11:01:58Z

10

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

edited Nov 19, 2018 at 11:01

answered Nov 19, 2018 at 10:46

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harsha W Over a year ago

Thanks @PeterB, This is what I was looking for

Gottfried Lesigang · Accepted Answer · 2018-11-19 10:08:01Z

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

Harsha W · Accepted Answer · 2020-12-14 03:34:08Z

0

Just enhancing @Peter B's answer

UPDATE X
SET    FromID = J.FromID
FROM   OPENJSON(@RelationshipType, '$.RelationshipType')
   WITH (ID INT, FromID INT, ToID INT) J

edited Dec 14, 2020 at 3:34

Harsha W

3,3867 gold badges54 silver badges92 bronze badges

answered Dec 12, 2020 at 13:43

sunny

213 bronze badges

Collectives™ on Stack Overflow

Update table using JSON in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related