CODEWITHSUNDEEP
sqljsonsql-serversql-server-2016json-value
DennisT
DennisT

Reputation: 45

Assistance with JSON functions in SQL Server 2016

I have a SQL Server table with the following format:

ID               int
JobParameters    nvarchar(max)

where JobParameters column contains for example:

{
    "Parameters": [
        {
            "SID": 35,
            "RPYID": 10
        }, {
            "SID": 36,
            "RPYID": 10
        }, {
            "SID": 39,
            "RPYID": 10
        }, {
            "SID": 46,
            "RPYID": 10
        }, {
            "SID": 58,
            "RPYID": 10
        }, {
            "SID": 65,
            "RPYID": 10
        }, {
            "SID": 71,
            "RPYID": 10
        }, {
            "SID": 72,
            "RPYID": 10
        }, {
            "SID": 73,
            "RPYID": 10
        }, {
            "SID": 74,
            "RPYID": 10
        }, {
            "SID": 78,
            "RPYID": 10
        }, {
            "SID": 80,
            "RPYID": 10
        }, {
            "SID": 81,
            "RPYID": 10
        }, {
            "SID": 82,
            "RPYID": 10
        }, {
            "SID": 88,
            "RPYID": 10
        }, {
            "SID": 90,
            "RPYID": 10
        }, {
            "SID": 96,
            "RPYID": 10
        }, {
            "SID": 407,
            "RPYID": 10
        }, {
            "SID": 408,
            "RPYID": 10
        }
    ]
}

I need to insert the SID and RPYID from the JobParameters (JSON) into another table.

So basically need to efficiently convert JSON value stored in one table to two columns in another table.

Upvotes: 0

Views: 32

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272256

Use OPENJSON like so:

SELECT ID, j.SID, j.RPYID
FROM t
CROSS APPLY OPENJSON(JobParameters, '$.Parameters')
WITH (   
    SID int '$.SID',  
    RPYID int '$.RPYID'
) AS j

Demo on db<>fiddle

Upvotes: 1

Related Questions