OPENJSON function in SQL Server

In the previous article, I have explained about different ways to import JSON into SQL server. One of the ways is using OPENJSON function. In this article, I will explain OPENJSON function in SQL Server with some examples.

OPENJSON is a table valued function that parses JSON document and transforms it into rows and columns. This function is introduced in SQL server 2016 version.

Syntax

OPENJSON( jsonExpression [ , jsonPath ] ) 
[ 
    WITH ( col1_Name type [column1_path] [AS JSON],
    [colN_Name type [columnN_path] [AS JSON]] ) 
]

Parameters

• jsonExpression – It is the input JSON text to transform it into tabular format.
• jsonPath- It is an optional expression that refers to an array or object in the JSON text.
• WITH Clause – It is an optional clause to define the output schema explicitly. It contains colName, type, JSON path and [AS JSON] elements.
• [AS JSON] – This option is used to return the object or array from the input text as JSON.

Now, let’s see some of the examples by using default schema and explicit schema methods

Examples

Example 1: This example describes the default schema method with all possible types.

DECLARE @JSON NVARCHAR(1000) = N'
    {"Initial": null, "EmpName":"Sean Paul", "EmpID":101, "IsPermanent": false, "Skills":["SQL","C#.NET","VB"], 
	"Address":{"Address1":"103 Greensboro Street","State":"MS", "Country":"USA"}}'

--Default schema
SELECT * FROM OPENJSON(@JSON)

Below is the result of the above query. It returns three columns – key, value and the type of each key-value pair. The number in the type column denotes the data type of the value column. It ranges from 0 through 5.

Example 2: This example explains how to access JSON array and object by specifying ‘jsonPath’ with default schema.

DECLARE @JSON NVARCHAR(1000) = N'
    {"Initial": null, "EmpName":"Sean Paul", "EmpID":101, "IsPermanent": false, "Skills":["SQL","C#.NET","VB"], 
	"Address":{"Address1":"103 Greensboro Street","State":"MS", "Country":"USA"}}'

SELECT * FROM OPENJSON(@JSON, '$.Skills')
SELECT * FROM OPENJSON(@JSON, '$.Address')

Example 3: In this example, let’s see the usage of explicit schema method.

DECLARE @JSON NVARCHAR(1000) = N'
    {"Initial": null, "EmpName":"Sean Paul", "EmpID":101, "IsPermanent": false, "Skills":["SQL","C#.NET","VB"], 
	"Address":{"Address1":"103 Greensboro Street","State":"MS", "Country":"USA"}}'

--Explicit schema
SELECT * FROM OPENJSON(@JSON)
WITH(EmpID INT, EmpName VARCHAR(25), IsPermanent BIT)

The above example returns the columns specified in the WITH clause. The name of the column should match with the name of the property in the JSON text. If the column name differs, then it returns NULL.

Example 4: This example explains renaming the column, retrieving array and objects with explicit schema.

DECLARE @JSON NVARCHAR(1000) = N'
    {"Initial": null, "EmpName":"Sean Paul", "EmpID":101, "IsPermanent": false, "Skills":["SQL","C#.NET","VB"], 
	"Address":{"Address1":"103 Greensboro Street","State":"MS", "Country":"USA"}}'

SELECT * FROM OPENJSON(@JSON)
WITH(EmployeeID INT '$.EmpID', EmployeeName VARCHAR(25) '$.EmpName', Skills VARCHAR(100) '$.Skills[0]', Address1 VARCHAR(50) '$.Address.Address1')

With explicit schema method, you can rename the column name however you want. In the above example, column ‘EmpID’ is renamed as ‘EmployeeID’. Also, you can specify ‘jsonPath’ to access arrays and objects in the JSON text.

Example 5: Usage of AS JSON option

DECLARE @JSON NVARCHAR(1000) = N'
    {"Initial": null, "EmpName":"Sean Paul", "EmpID":101, "IsPermanent": false, "Skills":["SQL","C#.NET","VB"], 
	"Address":{"Address1":"103 Greensboro Street","State":"MS", "Country":"USA"}}'

SELECT * FROM OPENJSON(@JSON)
WITH(EmployeeID INT '$.EmpID', EmployeeName VARCHAR(25) '$.EmpName', Skills NVARCHAR(MAX) '$.Skills' AS JSON, AddressObj NVARCHAR(MAX) '$.Address'  AS JSON)

You can use AS JSON option in the column definition to return a nested array or object from a JSON text. The type of the column must be specified as NVARCHAR(MAX).

Example 6: In this example, let’s see how to retrieve data from array of JSON objects.

DECLARE @JSON NVARCHAR(1000) = N'
    {"Employee": 
	[{"Initial": null, "EmpName":"Sean Paul", "EmpID":101, "IsPermanent": false, "Skills":["SQL","C#.NET","VB"], 
	"Address":{"Address1":"103 Greensboro Street","State":"MS", "Country":"USA"}},
	{"Initial": "A", "EmpName":"Peter Frank", "EmpID":201, "IsPermanent": true, "Skills":["MongoDB","PHP"], 
	"Address":{"Address1":"50 Lakes Street","State":"CA", "Country":"USA"}}
	]}'

SELECT * FROM OPENJSON(@JSON, '$.Employee')
WITH(EmployeeID INT '$.EmpID', EmployeeName VARCHAR(25) '$.EmpName', [State] VARCHAR(50) '$.Address.State', Skills NVARCHAR(MAX) AS JSON)

Refer OPENJSON (T-SQL)