The SQL Server OPENJSON function is used to parse JSON data and convert it into a tabular format that can be used for analysis and reporting.

The OPENJSON function takes a JSON string or a JSON file as input and returns a table that represents the data in the JSON document. The function can be used to extract specific values from the JSON data, as well as to create more complex queries that join JSON data with other tables.

Syntax

Here's an example of how to use the OPENJSON function to parse a JSON string:

DECLARE @json NVARCHAR(MAX)
SET @json = '{
"id": 1,
"name": "John Doe",
"email": "johndoe_mail"
}'

SELECT *
FROM OPENJSON(@json)
WITH (
id INT,
name VARCHAR(50),
email VARCHAR(50)
)

In this example, the JSON string represents a user with an ID, name, and email address. The OPENJSON function is used to extract these values and create a table with columns for each value. The WITH clause is used to specify the data types for each column.

The output of this query would be a single row with the values 1, "John Doe", and "johndoe_mail".

Example

The OPENJSON function can also be used to parse more complex JSON structures, such as arrays and nested objects. Here's an example of how to use the OPENJSON function to parse an array of user objects:

DECLARE @json NVARCHAR(MAX)
SET @json = '[
{ "id": 1, "name": "John Doe", "email": "johndoe_mail" },
{ "id": 2, "name": "Jane Smith", "email": "janesmith_mail" }
]'

SELECT *
FROM OPENJSON(@json)
WITH (
id INT,
name VARCHAR(50),
email VARCHAR(50)
)

In this example, the JSON string represents an array of two user objects. The OPENJSON function is used to extract the values for each object and create a table with multiple rows.

The OPENJSON function also supports the ability to extract nested objects and arrays using the CROSS APPLY operator. This can be useful when working with more complex JSON structures.

In summary, the SQL Server OPENJSON function is a powerful tool for working with JSON data in a relational database environment. It allows you to parse JSON data and convert it into a tabular format that can be used for analysis and reporting.