Modern applications, data often comes in JSON (JavaScript Object Notation) format — especially when integrating APIs or working with web and cloud platforms.
While JSON is great for flexibility, working with it directly inside SQL Server can be challenging.
SQL Server (from version 2016 onward) provides built-in JSON functions like OPENJSON(), JSON_VALUE(), and JSON_QUERY() that let you read, parse, and query JSON data just like tables.
DECLARE @JSON NVARCHAR(MAX) = '
{
"Company": "ABC Technology",
"Location": "New Delhi",
"Departments": [
{
"DeptID": 1,
"DeptName": "Engineering",
"Employees": [
{
"EmpID": 101,
"Name": "Surya",
"Title": "Software Engineer",
"Skills": ["C#", "SQL", "Azure"]
},
{
"EmpID": 102,
"Name": "Neha",
"Title": "DevOps Engineer",
"Skills": ["Docker", "Kubernetes", "Python"]
}
]
},
{
"DeptID": 2,
"DeptName": "Sales",
"Employees": [
{
"EmpID": 201,
"Name": "Ranjana",
"Title": "Sales Executive",
"Skills": ["Negotiation", "CRM", "Communication"]
}
]
}
]
}';
Parse Departments
SELECT
JSON_VALUE(@JSON, '$.Company') AS Company,
JSON_VALUE(@JSON, '$.Location') AS Location,
d.DeptID,
d.DeptName
FROM OPENJSON(@JSON, '$.Departments')
WITH (
DeptID INT '$.DeptID',
DeptName NVARCHAR(100) '$.DeptName'
) AS d;
Parse Employees
SELECT
JSON_VALUE(@JSON, '$.Company') AS Company,
d.DeptID,
d.DeptName,
e.EmpID,
e.Name,
e.Title
FROM OPENJSON(@JSON, '$.Departments')
WITH (
DeptID INT '$.DeptID',
DeptName NVARCHAR(100) '$.DeptName',
Employees NVARCHAR(MAX) AS JSON
) AS d
CROSS APPLY OPENJSON(d.Employees)
WITH (
EmpID INT '$.EmpID',
Name NVARCHAR(100) '$.Name',
Title NVARCHAR(100) '$.Title'
) AS e;
Parse Deeply
SELECT
d.DeptName,
e.Name AS EmployeeName,
s.value AS Skill
FROM OPENJSON(@JSON, '$.Departments')
WITH (
DeptName NVARCHAR(100) '$.DeptName',
Employees NVARCHAR(MAX) AS JSON
) AS d
CROSS APPLY OPENJSON(d.Employees)
WITH (
Name NVARCHAR(100) '$.Name',
Skills NVARCHAR(MAX) AS JSON
) AS e
CROSS APPLY OPENJSON(e.Skills) AS s;
🙏 Thank You for Reading!
Thank you for taking the time to read this blog!
If you have any questions or need help with something, feel free to drop a message in the comments or contact section. I’ll get back to you as soon as possible.
Happy Learning! 😊