Handling Nested JSON Arrays in SQL with OPENJSON

Query Nested JSON Arrays

Write a SQL query to extract elements from a nested JSON array.

Solution:

-- Extract elements from a nested JSON array.
SELECT 
    EmployeeID,
    JSON_VALUE(Profile, '$.Name') AS Name,
    JSON_VALUE(Skill.value, '$') AS Skill
FROM Employees
CROSS APPLY OPENJSON(Profile, '$.Skills') AS Skill;

Explanation:

1. Purpose of the Query :

1. The goal is to extract individual skills from a nested JSON array (Skills) within the Profile column.

2. Key Components :

1. OPENJSON : Parses JSON arrays into rows.
2. CROSS APPLY : Joins the parsed JSON data with the main table.
3. $.Skills : Specifies the path to the nested array.

3. Why use Nested JSON Queries? :

1. Nested JSON queries allow you to work with complex, hierarchical data structures stored in JSON format.

4. Real-World Application :

1. For example, in recruitment systems, you might use this query to analyze employee skill sets stored in JSON format.

Additional Notes:

• Use OPENJSON for parsing arrays and objects within JSON data.
• Use this exercise to teach how to handle deeply nested JSON structures.

For more Practice: Solve these Related Problems:

• Write a SQL query to extract individual project names from a nested JSON array stored in an employee's profile.
• Write a SQL query to parse and display all certifications from a nested JSON array in a professional profile table.
• Write a SQL query to extract elements from a JSON array representing a customer's order history.
• Write a SQL query to retrieve all languages spoken by users from a nested JSON array in a user preferences table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Combine PIVOT and UNPIVOT Operators.
Next SQL Exercise: Use LAG and LEAD Functions