The OPENJSON function in SQL Server is used to parse JSON data and return it in a tabular format, which can then be easily manipulated using SQL queries. Below is a structured approach to using OPENJSON to parse JSON data into rows and columns.
Basic Syntax
The basic syntax of the OPENJSON function is:
OPENJSON ( jsonString [, path ] )
- jsonString: The JSON data to be parsed, typically a JSON string or a column containing JSON data.
- path (optional): A path to a specific part of the JSON if you want to parse a nested object.
Example JSON Data
Let's consider an example JSON string:
[
{"id": 1, "name": "John", "age": 30},
{"id": 2, "name": "Jane", "age": 25},
{"id": 3, "name": "Doe", "age": 27}
]
Step 1: Open JSON Data
You can use OPENJSON to parse this JSON data. Here’s how you would do it in SQL Server:
DECLARE @json NVARCHAR(MAX) = '
[
{"id": 1, "name": "John", "age": 30},
{"id": 2, "name": "Jane", "age": 25},
{"id": 3, "name": "Doe", "age": 27}
]';
SELECT *
FROM OPENJSON(@json)
WITH (
id INT '$.id',
name NVARCHAR(50) '$.name',
age INT '$.age'
);
Step 2: Break Down the Query
1. Declare the JSON Variable: We declare a variable to hold our JSON data.
2. Select From OPENJSON:
- We use SELECT * FROM OPENJSON(@json), which will parse the JSON data and return it as rows.
3. Using the WITH Clause:
- The WITH clause allows you to specify the columns and their data types to be returned.
- In this case, we define three columns: id, name, and age, with their respective data types.
Results
Using the example above, the output would look like this:
| id | name | age |
|----|------|-----|
| 1 | John | 30 |
| 2 | Jane | 25 |
| 3 | Doe | 27 |
Additional Features
1. Nested JSON Objects: If you have nested JSON objects, you can specify the path using the WITH clause. For example:
{
"employees": [
{"id": 1, "name": "John", "age": 30},
{"id": 2, "name": "Jane", "age": 25}
]
}
You can parse it like this:
DECLARE @json NVARCHAR(MAX) = '{
"employees": [
{"id": 1, "name": "John", "age": 30},
{"id": 2, "name": "Jane", "age": 25}
]
}';
SELECT *
FROM OPENJSON(@json, '$.employees')
WITH (
id INT '$.id',
name NVARCHAR(50) '$.name',
age INT '$.age'
);
2. As JSON Column: You can also include a column that returns the JSON representation of each row. You achieve this with the AS JSON clause.
In summary, the OPENJSON function is a powerful way to parse and manipulate JSON data in SQL Server, making it easier to work with semi-structured data.