To parse JSON data in SQL Server, there is a function called OPENJSON can
be used in SQL Script.
Syntax: OPENJSON( [jsondata], [path]) [ with Clause ] .
Parameter jsondata is your json data and parameter path is the node of your jsondata.
'$' is used to hold the root node.
But before using OPENJSON function in your SQL Server, you must set
compatibility level as 130 at least in your database to support
OPENJSON function.
Example:
ALTER DATABASE MyDatabase SET COMPATIBILITY_LEVEL = 130
Parse JSON Data
Example 1:
DECLARE @Json NVARCHAR(MAX)
SELECT @Json = '
{
"StudentList":
[
{
"Id":1,
"FirstName":"Amalay",
"LastName":"Verma",
"Class":10,
"Marks":61.5
},
{
"Id":2,
"FirstName":"Saurabh",
"LastName":"Sharma",
"Class":11,
"Marks":82.7
},
{
"Id":3,
"FirstName":"Kanchan",
"LastName":"Pandey",
"Class":10,
"Marks":90.5
}
]
}'
SELECT Id, FirstName, LastName, Class, Marks
FROM OPENJSON (@Json, '$.StudentList')
WITH
(
Id INT,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Class INT,
Marks DECIMAL(3,1)
) AS StudentList;
Example 2:
DECLARE @json NVARCHAR(1000)
SELECT @json =
N'
{
"OrderHeader":
[
{
"OrderId": 100,
"CustomerId": 101,
"OrderDetail":
[
{
"ProductId": 2000,
"UnitPrice": 350
},
{
"ProductId": 3000,
"UnitPrice": 450
}
]
}
]
}'
SELECT
OrderId,
CustomerId,
ProductId1,
UnitPrice1,
ProductId2,
UnitPrice2
FROM OPENJSON (@json, '$.OrderHeader')
WITH (
OrderId INT
'$.OrderId',
CustomerId INT '$.CustomerId',
ProductId1 INT
'$.OrderDetail[0].ProductId',
UnitPrice1 INT
'$.OrderDetail[0].UnitPrice',
ProductId2 INT
'$.OrderDetail[1].ProductId',
UnitPrice2 INT
'$.OrderDetail[1].UnitPrice'
) AS Orders
Example 3:
DECLARE @Json NVARCHAR(MAX)
SELECT @Json = '
{
"ProfileId":1,
"Name":"P 1",
"BaseGroups":
[
{
"GroupId":11,
"GroupName":"G 11",
"BaseTiles":
[
{
"TileId":111,
"Name":"T 111",
"State":0
},
{
"TileId":112,
"Name":"T 112",
"State":0
}
],
"State":0
},
{
"GroupId":12,
"GroupName":"G 12",
"BaseTiles":
[
{
"TileId":121,
"Name":"T 121",
"State":0
},
{
"TileId":122,
"Name":"T 122",
"State":0
}
],
"State":0
}
],
"State":1
}'
SELECT ProfileId, Name, State
FROM OPENJSON (@Json, '$')
WITH
(
ProfileId INT,
Name VARCHAR(255),
State INT
) AS ProfileDetails;
Comments
Post a Comment