In SQL Server, the OPENJSON function is used to parse JSON data. It provides a convenient way to convert JSON text into a relational format that can be used in queries. Here's how you can use the OPENJSON function to extract data from JSON:
Basic Syntax
OPENJSON ( json_expression [, path ] )
- json_expression: This is the JSON string or JSON object you want to parse.
- path: This is optional. It specifies the path to a specific element or object within the JSON document.
Example of Using OPENJSON
Suppose you have the following JSON data representing a list of employees:
[
{ "Id": 1, "Name": "John Doe", "Department": "HR" },
{ "Id": 2, "Name": "Jane Smith", "Department": "IT" },
{ "Id": 3, "Name": "Sam Brown", "Department": "Finance" }
]
You can store this JSON in a SQL Server table or variable and then parse it using OPENJSON. Here’s how to do it step by step:
1. Storing JSON in a variable:
DECLARE @json NVARCHAR(MAX) = '[
{ "Id": 1, "Name": "John Doe", "Department": "HR" },
{ "Id": 2, "Name": "Jane Smith", "Department": "IT" },
{ "Id": 3, "Name": "Sam Brown", "Department": "Finance" }
]';
2. Using OPENJSON to Parse the JSON:
You can use the OPENJSON function to read the JSON data and convert it into tabular format. Here’s an example query:
SELECT *
FROM OPENJSON(@json)
WITH (
Id INT '$.Id',
Name NVARCHAR(100) '$.Name',
Department NVARCHAR(100) '$.Department'
) AS Employees;
Explanation of the Query:
- OPENJSON(@json): This function call takes the JSON stored in the @json variable.
- WITH (...): This clause defines the schema of the output, specifying how to extract each property from the JSON objects. You define the column names, data types, and JSON paths to the respective elements.
Example Output
The result of executing the query would look something like this:
| Id | Name | Department |
|----|------------|------------|
| 1 | John Doe | HR |
| 2 | Jane Smith | IT |
| 3 | Sam Brown | Finance |
Additional Options
- Key-Value Pairs: If you don't specify a WITH clause, OPENJSON will return the key-value pairs in the JSON data.
- Using Nested Objects: If your JSON contains nested objects or arrays, you can specify the paths accordingly in your WITH clause or by using the path argument.
- Cross Apply / Join: You can also use CROSS APPLY to join OPENJSON with other tables.
Example with CROSS APPLY
If you wanted to join the JSON data with another table, you can do something like this:
SELECT e.Id, e.Name, e.Department, d.DepartmentHead
FROM OPENJSON(@json) WITH (
Id INT '$.Id',
Name NVARCHAR(100) '$.Name',
Department NVARCHAR(100) '$.Department'
) AS e
JOIN Departments d ON e.Department = d.DepartmentName;
This would join the parsed employee data with a hypothetical Departments table based on the department name.
Conclusion
OPENJSON is a powerful tool for working with JSON data in SQL Server, allowing you to easily integrate JSON into your relational queries. Be sure to explore the different ways it can be combined with other queries for more complex data processing scenarios.