Accessing a nested json formatted text using OpenJSON in SQL Server 2016

OpenJSON function is available for database compatibility levle is 130. That means you have a SQL Server 2016 installed.

Here is the syntax for OpenJSON tabled valued function.

OpenJson is a table-valued function that parses JSON text and returns objects in rows and columns.  You can use OpenJson function in a FROM clause of a Transact-SQL Statements like any table, view or table-value function.

Here is a simple example of a JSON text:
{"wclass":{"student":["jay", "john", "sam"]}}

Here is a code to use the OpenJson funtion.
------
declare @json nvarchar(150)
SET @json=N'{"wclass":{"student":["jay", "john", "sam"]}}';
SELECT * From OPENJSON(@json)
-----------------
This returns a table with three columns: key, value and type for 'wclass'
The type is the JSON type of the value.
In the above case the type=5 represents that {"student":["jay", "john", "sam"]} is Json Object.

Now we access the 'student' key of the json text as shown:
-------------
declare @json nvarchar(150)
SET @json=N'{"wclass":{"student":["jay", "john", "sam"]}}';
SELECT * From OPENJSON(@json,'$."wclass"')
-----------
This returns a table with the following:

Key: student
value: ["jay', "john", "sam"]
type: 4 which means it is a Json Array

Now we go into the array and look into it as shown:
========
declare @json nvarchar(150)
SET @json=N'{"wclass":{"student":["jay", "john", "sam"]}}';
SELECT * From OPENJSON(@json,'$."wclass"."student"')
=============
Now we get the Key, value and type as shown.

OpenJsonTypeColumn2.PNG
Each of the values are of type 1 ( that is they are of type string).

Here is table showing the type (an integer) of the return value of OpenJson function.