Import Nested JSON Files to SQL Server with SSIS

By: Nat Sundar   |   Updated: 2018-04-10   |   Comments (1)   |   Related: 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".

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.

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.

 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.

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.

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.

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.

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.

 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.

 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;
   }
}			

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

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips