TSQL: Mastering Nested JSON Parsing with OPENJSON Function: JSON Array Examples and CROSS APPLY Techniques
In this article, we will look at advanced JSON manipulation within SQL Server. Our focus will be on comprehending the OPENJSON function and its application, showcasing how SQL Server can effectively harness the potential of JSON-derived data.
EmployeeData Table with a JSON Column of NVARCHAR(MAX) Type
You can use this Fiddle to follow along and practice nested JSON parsing
with OPENJSON Function
Let's use an EmployeeData table that contains 5 sample records. Each record represents an employee with detailed information stored in JSON format under the EmployeeJSON column. The JSON data includes various attributes such as salary, hire date, contact information, skills, and addresses.
EmployeeData table:
We will create a T-SQL query to retrieve data from EmployeeData table where each record contains complex JSON data in the EmployeeJSON column. The query uses the OPENJSON function to parse and extract various attributes from the JSON data.
Step 1: Parse Root Level Attributes with OPENJSON() and CROSS APPLY
The CROSS APPLY operator is used to apply the OPENJSON function to parse the JSON content in the EmployeeJSON column for each record. The WITH clause defines how the JSON data is mapped to individual columns in the result set. In this case:
EmployeeId,EmployeeName,Salary,DateHired,ContactInfo,Skills, andAddressesare extracted from the JSON object and given aliases within the subqueryROOT_JS.
You might come across JSON key names containing characters such as a dollar sign $ or spaces ($Salary and Contact Info in our example). When these characters are present, they can lead to errors when using the OPENJSON function. You can resolve this easily by enclosing the problematic key names within double quotation marks ("$Salary" and "Contact Info").
We add the AS JSON keyword to ContactInfo, Skills, and Addresses columns to set them as JSON fragments. This will enable them to be used with a subsequent call to OPENJSON.
SELECT EA.EmployeeId,
EA.EmployeeName,
EA.Comments,
ROOT_JS.Salary,
ROOT_JS.DateHired
FROM dbo.EmployeeData AS EA
CROSS APPLY
OPENJSON(EA.EmployeeJSON)
WITH
(
EmployeeId INT '$.EmployeeId',
EmployeeName VARCHAR(100) '$.EmployeeName',
Salary INT '$."$Salary"',
DateHired DATE '$.HireDate',
ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
Skills NVARCHAR(MAX) '$.Skills' AS JSON,
Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
) AS ROOT_JS
Step 2: Parse Contact Info JSON Object with OPENJSON() and OUTER APPLY
The first OUTER APPLY extracts contact information (Email and Phone) from the ContactInfo JSON object, giving it an alias CONTACT.
SELECT EA.EmployeeId,
EA.EmployeeName,
EA.Comments,
ROOT_JS.Salary,
ROOT_JS.DateHired,
CONTACT.Email,
CONTACT.Phone
FROM dbo.EmployeeData AS EA
CROSS APPLY
OPENJSON(EA.EmployeeJSON)
WITH
(
EmployeeId INT '$.EmployeeId',
EmployeeName VARCHAR(100) '$.EmployeeName',
Salary INT '$."$Salary"',
DateHired DATE '$.HireDate',
ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
Skills NVARCHAR(MAX) '$.Skills' AS JSON,
Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
) AS ROOT_JS
OUTER APPLY
(
SELECT *
FROM
OPENJSON(ROOT_JS.ContactInfo)
WITH
(
Email VARCHAR(50) '$.Email',
Phone VARCHAR(50) '$.Phone'
)
) AS CONTACT
Step 3: Parse Skills JSON Array with OPENJSON() and OUTER APPLY
The second OUTER APPLY calculates skill-related information (SkillCount and comma-separated SkillList) from the Skills JSON array, giving it an alias SKILLS.
Since the Skills JSON array does not contain JSON objects, we can simply reference the items with the $ dollar sign in the WITH clause.
SELECT EA.EmployeeId,
EA.EmployeeName,
EA.Comments,
ROOT_JS.Salary,
ROOT_JS.DateHired,
CONTACT.Email,
CONTACT.Phone,
SKILLS.SkillCount,
SKILLS.SkillList
FROM dbo.EmployeeData AS EA
CROSS APPLY
OPENJSON(EA.EmployeeJSON)
WITH
(
EmployeeId INT '$.EmployeeId',
EmployeeName VARCHAR(100) '$.EmployeeName',
Salary INT '$."$Salary"',
DateHired DATE '$.HireDate',
ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
Skills NVARCHAR(MAX) '$.Skills' AS JSON,
Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
) AS ROOT_JS
OUTER APPLY
(
SELECT *
FROM
OPENJSON(ROOT_JS.ContactInfo)
WITH
(
Email VARCHAR(50) '$.Email',
Phone VARCHAR(50) '$.Phone'
)
) AS CONTACT
OUTER APPLY
(
SELECT COUNT(*) AS SkillCount,
STRING_AGG(Skill, ', ')WITHIN GROUP(ORDER BY Skill) AS SkillList
FROM
OPENJSON(ROOT_JS.Skills)
WITH
(
Skill VARCHAR(50) '$'
)
) AS SKILLS
Step 4: Parse Addresses JSON Array with OPENJSON() and OUTER APPLY
The third OUTER APPLY computes address-related information (AddressCount and concatenated AddressList) from the Addresses JSON array, giving it an alias ADDRESSES. The address components (AddressType, Street, City, State, PostalCode) are combined and formatted within the STRING_AGG function.
The final ORDER BY clause sorts the result set by the EmployeeId.
SELECT EA.EmployeeId,
EA.EmployeeName,
EA.Comments,
ROOT_JS.Salary,
ROOT_JS.DateHired,
CONTACT.Email,
CONTACT.Phone,
SKILLS.SkillCount,
SKILLS.SkillList,
ADDRESSES.AddressCount,
ADDRESSES.AddressList
FROM dbo.EmployeeData AS EA
CROSS APPLY
OPENJSON(EA.EmployeeJSON)
WITH
(
EmployeeId INT '$.EmployeeId',
EmployeeName VARCHAR(100) '$.EmployeeName',
Salary INT '$."$Salary"',
DateHired DATE '$.HireDate',
ContactInfo NVARCHAR(MAX) '$."Contact Info"' AS JSON,
Skills NVARCHAR(MAX) '$.Skills' AS JSON,
Addresses NVARCHAR(MAX) '$.Addresses' AS JSON
) AS ROOT_JS
OUTER APPLY
(
SELECT *
FROM
OPENJSON(ROOT_JS.ContactInfo)
WITH
(
Email VARCHAR(50) '$.Email',
Phone VARCHAR(50) '$.Phone'
)
) AS CONTACT
OUTER APPLY
(
SELECT COUNT(*) AS SkillCount,
STRING_AGG(Skill, ', ')WITHIN GROUP(ORDER BY Skill) AS SkillList
FROM
OPENJSON(ROOT_JS.Skills)
WITH
(
Skill VARCHAR(50) '$'
)
) AS SKILLS
OUTER APPLY
(
SELECT COUNT(*) AS AddressCount,
STRING_AGG(CONCAT_WS(' ', AddressType + ':', Street, City, State, PostalCode), ' | ')WITHIN GROUP(ORDER BY AddressType DESC) AS AddressList
FROM
OPENJSON(ROOT_JS.Addresses)
WITH
(
AddressType VARCHAR(10) '$.Type',
Street VARCHAR(10) '$.Street',
City VARCHAR(10) '$.City',
State VARCHAR(10) '$.State',
PostalCode VARCHAR(10) '$.PostalCode'
)
) AS ADDRESSES
ORDER BY EA.EmployeeId
OPENJSON() Function Summary
In summary, this SQL query showcases the power of T-SQL's OPENJSON function (available in SQL Server 2016 or later) along with STRING_AGG, CONCAT_WS, CROSS APPLY, and `OUTER APPLY', to efficiently extract, transform, and present data from JSON objects and arrays. It allows for the retrieval of comprehensive employee information, including contact details, skills, and addresses, all within a single structured result set. This type of query is particularly useful when working with semi-structured data stored in JSON format.