Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Import Nested JSON Files to SQL Server with SSIS


By:   |   Read Comments (1)   |   Related Tips: More > Import and Export

Problem

In a previous tip we learned how to import JSON files into SQL Server using SSIS.  However, I have been supplied a JSON file which contains multiple nested JSON objects. Hence, I would like to learn how to import nested JSON objects into SQL Server using SQL Server Integration Services.

Solution

In this tip, I will show how to import two nested JSON object files into SSIS using a couple of examples.

Import JSON File into SQL Server - Example #1

The below image represents a simple JSON object which contains a nested JSON object "links". The "links" JSON object has 5 attributes namely "self", "first", "last", "next" and "prev".

LInks JSON File - Description: LInks JSON File

It is observed that the attribute "prev" has a value of "null". In JSON null or no values are expressed as "null". This example is the same as the “Orders” JSON file mentioned in the previous tip. Hence, we will be following similar procedures to load the file.

As a first step, let’s create a data flow task and add a script component to source the JSON file. Once this is done, let's add the output columns for the JSON object.

The columns "First", "Last", "Next", "Prev" and "Self" have been added as output columns with the datatype string.

Output Columns - Description: Output Columns

Now we need to define a class to store the value of "Links" object at runtime. If you observed very closely on the JSON viewer, it is evident that there is a root level JSON object.

The root level JSON object contains the "links" JSON object. As there are two objects, we need to create two classes. A "LinkSubItem" class will be defined to store the value of "links" attributes.

A petition class will be defined to represent the root level JSON object. The petition class will have a property to store the values of links. In this way, an object of type Petition will store the root level object with "links" as an inner most object. Hence once the JSON file has been loaded and stored as a petition object, we can access all the properties using .Net libraries.

Petition Class Definition - Description: Petition Class Definition
 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PetitionNamespace
{
   class Petition
   {
      public LinkSubItem links { get; set; }
   }
   public class LinkSubItem
   {
      public string self { get; set; }
      public string first { get; set; }
      public string last { get; set; }
      public string next { get; set; }
      public string prev { get; set; }
   }
}			

The above class definition will help you to create the class for Petition.

Deserialization

We have learned about deserialization in the last tip. Now we can deserialize and load the JSON file into an object of type petition. As there is only one JSON object at the root we don’t need to define an array. A simple definition of petition object is enough. Once the petition object has been created we can access the inner most object “links” using the notation "petition.links".

Hence the attributes of link object can be accessed by "petition.links.self". The below mentioned script will help you to deserialize the JSON object.

Deserialization - Description: Deserialization
public override void CreateNewOutputRows()
{
   /*
   Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
   For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
   */
   String jsonFileContent = File.ReadAllText(@"C:\SSIS_JSON\Petition.JSON");
   JavaScriptSerializer js = new JavaScriptSerializer();
   Petition petition = js.Deserialize<Petition>(jsonFileContent);
   Output0Buffer.AddRow();
   Output0Buffer.self = petition.links.self;
   Output0Buffer.First = petition.links.first;
   Output0Buffer.Last = petition.links.last;
   Output0Buffer.Next = petition.links.next;
   Output0Buffer.prev = petition.links.prev;
}			

After the successful execution of the package, we can see a record in the data pipe line as shown in the below picture.

Data Viewer - Description: Data Viewer

Import JSON File into SQL Server - Example #2

In example #1, we had a quick look at a simple example for a nested JSON document. Now let’s have a look at complex example on the nested JSON file.

In this example, at root level we have a single object "data". The data object contains the value as array and it has two petition objects. Each petition object has three attributes namely type, id and links. Both the attributes type and id are of string datatype and the links attribute is of type object. The JSON object viewer in the below image represents the object structure.

Petition JSON File - Description: Petition JSON File

Overall, we have three property details to collect from the supplied JSON and they are "id", "type" and "Link". So, let’s create output columns for these attributes with the data type "string" as mentioned in the below picture.

Petition Output Columns - Description: Petition Output Columns

By closely observing the JSON viewer, we can see that there are three objects in the supplied JSON document. The inner most object is "links" where it has the property "self" which holds the actual link for the petition. The next level in the hierarchy is data array item. An item has three attributes and they are "type", "id" and "links". A data item is a root level object that holds the array of “datasubitems” as its property.

To deserialize this JSON, we need three classes. The inner most class "SelfLink" represents the "links" item. The “DataSubItem” represents each item in the array. It is observed that the “datasubitem” has a property "links" which will return the object of type "Selflink" which contains the link details.

Finally, the “dataitem” class represents the root level object in the JSON file. This root object has a property "data" which will return a collection of subitem.

Petition Class Definition - Description: Petition Class Definition
 using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;

namespace PetitionNamespace
{
   public class DataItem
   {
      public Collection<DataSubItem> data { get; set; }
   }
   public class DataSubItem
   {
      public string type { get; set; }
      public string id { get; set; }
      public Selflink links { get; set; }
   }
   public class Selflink
   {
      public string self { get; set; }
   }
}			

Once the  class has been defined, we can deserialize the JSON file and store the file content of type "DataItem". The “dataitem” object contains a collection of “datasubitmes”. Hence, we need to iterate thru “datasubitems” to collect the details of the attributes. This is achieved by using the foreach loop construct in C#. As the attributes "type", "id" are the simple properties they can be extracted directly from the “datasubitem”. However, the “datasubitem” has "links" object as its attribute. Hence, we need to extract the "links" object from “datasubitem” to collect the "self" link.

The below script will help you to deserialize and extract all the data contents of the JSON file.

Deserialization - Description: Deserialization
 public override void CreateNewOutputRows()
{
   /*
   Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
   For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
   */
   String jsonFileContent = File.ReadAllText(@"C:\SSIS_JSON\Petition.JSON");
   JavaScriptSerializer js = new JavaScriptSerializer();
   DataItem dataItem = js.Deserialize<DataItem>(jsonFileContent);

   foreach (DataSubItem dataSubItem in dataItem.data)
   {
      Output0Buffer.AddRow();
      Output0Buffer.type = dataSubItem.type;
      Output0Buffer.id = dataSubItem.id;
      Selflink selflink = dataSubItem.links;
      Output0Buffer.Link = selflink.self;
   }
}			
Data Viewer - Description: Data Viewer

Summary

In this tip, we learned about importing nested JSON data files using SQL Server Integration Services. Also, we have learned about deserializing nested JSON into JSON runtime objects.

Next Steps
  • Learn JSON basics with this tip
  • Challenge your JSON knowledge with this tip


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 19, 2018 - 11:33:44 AM - Mayur Back To Top

 I am trying to import a file which contains 2 arrays as column type along with other columns. Any idea how to import that?

e.g. 

{

  "responses": [

    {

      "tags": [ "sales" ],

  "areas_for_improvement": [ ],

      "customer_custom_id": null,

      "response_received_at": "2018-09-13T03:01:19.680Z",

      "twitter_follow_eligible": false,


Learn more about SQL Server tools