What’s on our mind?

Exsilio Solutions is proud to introduce our new blog! Not only is this forum completely integrated with all our social networks, but it is also 100% responsive. Now, you can take Exsilio with you on your phone, tablet, and desktop - redefine what you thought possible!

SQL Server 2016 – How to load data into SQL Server using JSON Functionality

SQL 2016 brings in many new features such as Data Masking, Always Encrypted, multiple TempDB database files, Query Store, JSON support, Row Level Security, R, and Stretch Database etc. Today we are going to focus on one of my favorite which is JSON support.

**Please also see Part 2 of this post for info on the ISJSON Functionality.

SQL 2016 brings in many new features such as Data Masking, Always Encrypted, multiple TempDB database files, Query Store, JSON support, Row Level Security, R, and Stretch Database etc. Today we are going to focus on one of my favorite which is JSON support.

I was recently assigned a task here at Exsilio to load data from an Azure storage account, where backup files are saved in JSON format, to SQL Server. The task was a bit tricky. First, I wasn’t familiar with JSON file format and secondly, I wasn’t familiar with how to convert and load to SQL server in table –rows and column format.

SQL 2016 came in as blessing for me that time. I used Azure data factory to copy the file from storage account to my local drive and then used SQL 2016 JSON functionality to convert and update SQL server with that data. Today we will learn some basic methods of loading JSON data to SQL server through SSMS.

JSON – stands for Java Script Object Notation. With SQL 2016 we can now load JSON data to SQL database engine from various applications. It provides the ability to parse JSON formatted data enabling us to store in relational format. It’s difficult to analyze and generate reports when directly used. With built in JSON features into SQL server it’s much easier to load, query and analyze this data.

There are various SQL server built in functions for JSON such as OPENJSON, ISJSON, JSON_VALUE etc. In this post we will talk about the first one that is OPENJSON.

OPENJSON: This is a table-value function that parses JSON text and returns objects and properties in JSON as rows and columns. With this feature we can use OPENJSON in FROM clause of TSQL query like any other table or view.

It takes a single JSON object or collection of JSON and transforms them into one or multiple rows.

You can specify schema of rows that will be returned by OPENJSON function using WITH clause as shown below in examples.

This explicit schema defines the structure of the output.

When you use OPENJSON function without specified schema i.e. without WITH clause, the function returns a table with 3 columns – name of property in the input object, value and type. Type is basically int value what has following values

Value of Type Column JSON data type
0 Null
1 String
2 Int
3 True/false
4 Array
5 object

 

Below is the sample data in JSON format.

JSON

  {
    "Type": "Test",
    "Version": 1.0,
    "OrderDetail": {
      "OrderId": "ABC001",
      "ItemPurchase": "Pens",
      "Created": {
        "Date": "2016-07-10",
        "By": "Bob"
      },
      "LastUpdated": {
        "Date": "2016-07-11",
        "By": "Bob"

      },
      "Title": "JsonTest"
    }
  }'

Now we will be using OPENJSON to read unstructured data and convert to rows and column format used by SQL Server.

Querying JSON data using variable

DECLARE @Json nvarchar(max)

SET @Json = N'
  {
    "Type": "Test",
    "Version": 1.0,
    "OrderDetail": {
      "OrderId": "ABC001",
      "ItemPurchase": "Pens",
      "Created": {
        "Date": "2016-07-10",
        "By": "Bob"
      },
      "LastUpdated": {
        "Date": "2016-07-11",
        "By": "Bob"

      },
      "Title": "JsonTest"
    }
  }'

SELECT * FROM OPENJSON(@Json)
WITH(TYPE varchar(100) '$.Type',
Version varchar(100) '$.Version',
CreatedDate varchar(100) '$.OrderDetail.Created.Date',
CreatedBy varchar(100) '$.OrderDetail.Created.By',
LastUpdatedDate varchar(100) '$.OrderDetail.LastUpdated.Date',
LastUpdatedBy varchar(100) '$.OrderDetail.LastUpdated.By',
Title varchar(100) '$.OrderDetail.Title')

Output:

laxmiblog1

Same query when returned without WITH clause will show below results

laxmiblog2

You can see the difference how results got converted to Key value pair along with Type which shows you where its string\int\object.

Next, let’s say we need to bring in specific fields and not all, for the given example we are only interested in OrderDetail and not the TypeVersion then we will be using below query to get the data.

DECLARE @Json nvarchar(max)

SET @Json = N'
{
    "Type": "Test",
    "Version": 1.0,
    "OrderDetail": {
      "OrderId": "ABC001",
      "ItemPurchase": "Pens",
      "Created": {
        "Date": "2016-07-10",
        "By": "Bob"
      },
      "LastUpdated": {
        "Date": "2016-07-11",
        "By": "Bob"

      },
      "Title": "JsonTest"
    }
  }'

SELECT * FROM OPENJSON(@Json,'$.OrderDetail')
WITH(
CreatedDate varchar(100) '$.Created.Date',
CreatedBy varchar(100) '$.Created.By',
LastUpdatedDate varchar(100) '$.LastUpdated.Date',
LastUpdatedBy varchar(100) '$.LastUpdated.By',
Title varchar(100) '$.Title')

laxmiblog3

As shown above, I select all the fields within OrderDetail section of JSON data and as parent, OrderDetail is defined within OPENJSON, we don’t need to use it while referencing the columns. [eg: instead of using ‘$.OrderDetail.Created.Date’ we will use ‘$.Created.Date’]

You can also create a stored procedure in a similar way and load the data on a dailyweekly basis as per your job requirement.

SQL 2016 new JSON functionality solved my problem and thus I thought to share. You may have better examples or situations of working with JSON documents which you can share via comments. Thank you for your time and feedback.

Please see Part 2 of this post for info on the ISJSON Functionality.

Any thoughts?



Loading more content...