Sunday, November 6, 2016

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"]}}';
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.

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.

Get the March 2018 Power BI update

The update is out and you can get it from here : You can also get it from Windows Store if you are running Windows 10. Well what is n...