Handling JSON Arrays in SQL Using OPENJSON

Handle JSON Arrays with Nested Queries

Write a SQL query to extract all skills from a nested JSON array and count their occurrences.

Solution:

-- Extract all skills from a nested JSON array and count their occurrences.
SELECT 
    Skill.value AS SkillName,
    COUNT(*) AS SkillCount
FROM Employees
CROSS APPLY OPENJSON(Profile, '$.Skills') AS Skill
GROUP BY Skill.value;

Explanation:

1. Purpose of the Query :

1. The goal is to extract individual skills from a nested JSON array (Skills) and count how often each skill appears.

2. Key Components :

1. OPENJSON(Profile, '$.Skills') : Parses the nested JSON array into rows.
2. CROSS APPLY : Joins the parsed JSON data with the main table.
3. GROUP BY Skill.value : Groups skills to calculate their counts.

3. Why use Nested JSON Queries? :

1. This approach allows you to analyze semi-structured data stored in JSON arrays.

4. Real-World Application :

1. For example, in recruitment systems, you might use this query to identify the most common skills among employees.

Additional Notes:

• Ensure that the JSON structure is consistent across rows.
• Use this exercise to teach how to analyze nested JSON arrays.

For more Practice: Solve these Related Problems:

• Write a SQL query to extract all project names from a nested JSON array and count their occurrences.
• Write a SQL query to identify the most frequently mentioned hobbies in a JSON array stored in a user profile table.
• Write a SQL query to parse a JSON array of transactions and calculate the total amount spent per category.
• Write a SQL query to extract all elements from a JSON array representing user preferences and group them by type.

Go to:

• Master Advanced SQL Exercises Home ↩
• SQL Exercises Home ↩

PREV : Use STRING_AGG to Concatenate Strings.
NEXT : Use GROUPING SETS for Multi-Level Aggregation.

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