Extract JSON Fields in SQL Using JSON_VALUE and JSON_QUERY

Query JSON Data

Write a SQL query to extract specific fields from a JSON column.

Solution:

-- Extract fields from a JSON column.
SELECT 
    EmployeeID,
    JSON_VALUE(Profile, '$.Name') AS Name,
    JSON_VALUE(Profile, '$.Age') AS Age,
    JSON_QUERY(Profile, '$.Skills') AS Skills
FROM Employees;

Explanation:

• Purpose of the Query :
• The goal is to extract specific fields (Name, Age, Skills) from a JSON column (Profile) in the Employees table.
• Key Components :
• JSON_VALUE : Extracts scalar values (e.g., strings, numbers).
• JSON_QUERY : Extracts complex objects or arrays.
• $.FieldName : Specifies the JSON path to the desired field.
• Why use JSON Handling? :
• JSON support allows flexible storage and querying of semi-structured data.
• Real-World Application :
• For example, in modern applications, you might use this query to retrieve user profile details stored in JSON format.

Additional Notes:

• JSON handling is supported in many modern databases (e.g., SQL Server, PostgreSQL, MySQL).
• Use this exercise to teach how to work with semi-structured data in relational databases.

For more Practice: Solve these Related Problems:

• Write a SQL query to extract the address field from a JSON column containing customer details.
• Write a SQL query to retrieve the list of hobbies from a nested JSON array stored in a user profile table.
• Write a SQL query to extract the phone number field from a JSON object representing contact information.
• Write a SQL query to parse and display all elements of a JSON array stored in a column named "tags".

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

Previous SQL Exercise: Unpivot Data Using UNPIVOT Operator.
Next SQL Exercise: Use NTILE() for Data Bucketing.