When working with JSON data, a scenario may arise where there could be a format of a JSON Document that makes it challenging to extract the data into a typical tabular form. One of these formats is Key-Value Pairs that some APIs use. In this blog post, we will discuss using the "Key Value Pivot" Output type found in our JSON Source/Extract Components to parse Key/Value pairs from JSON documents with ease.
JSON Key Value Pair Format
First, to demonstrate the difference between a typical JSON format and the Key/Value pair structure, consider the following typical JSON Document design:
[
{
"Id":173,
"FirstName":"Rudolph",
"LastName":"Glover",
"Gender":"Male",
"Email":"[email protected]"},
"City":"Kampong",
"Country":"Vietnam"
}
]
When working with a similar document design, as shown above, we can directly associate the column name with the JSON Node name, and the Value of each node will be the data value under its corresponding column. This format can be easily parsed into a tabular form in SSIS. However, the Key/Value structure could contain the same data but in a different way. To understand the difference, consider the following JSON Document instead:
[
{
"Id":173,
"properties":[
{
"Name":"FirstName",
"Value":"Rudolph"
},
{
"Name":"LastName",
"Value":"Glover"
},
{
"Name":"Gender",
"Value":"Male"
},
{
"Name":"Email",
"Value":"[email protected]"
},
{
"Name":"City",
"Value":"Kampong"
},
{
"Name":"Country",
"Value":"Vietnam"
}
]
}
]
As we can see from the example above, the Column names are actually values for the "Name" node in the properties array. In contrast, the "Value" node will contain the data values of the corresponding Column based on the value assigned to the Name node. This structure makes it challenging to extract this JSON document into a typical tabular format. For instance, if we try to extract data from this document in SSIS, it will be retrieved as follows:
As we can see from the table shown above, this may not be the standard tabular format we expect to integrate the data from our JSON document to another database system or application.
Therefore, since version 20.1 of our SSIS Productivity Pack, we have added a new "Key Value Pivot" Output Type in our JSON Source/Extract components, which can be used to work with these types of JSON document designs.
Using the "Key Value Pivot" feature in a JSON Source/Extract Component
First, when working with our JSON Source Component, we need to configure the Data Source page of the component. For the Connection Manager property, we can choose the <<Local File>> option when working with a JSON file located on our file system. On the other hand, If we are receiving a JSON Document from an API, then we would choose an existing HTTP Connection Manager instead.
In this example, we are reading our JSON Document from a local file, and we have selected the file location in the Local File Path property, as shown below:
Once this has been done, we can set the design of the JSON document in the Document Designer Page of the JSON Source/Extract Component. We do this by defining each node manually, or we can use the Import feature of our JSON/Extract component to infer the design of the JSON Document based on a sample JSON file. Then, we can perform the necessary changes in the Document Designer settings to read the unique Key/Value Pair format.
After the import is complete, the component will set the Design of the document as follows, using the JSON document we have mentioned earlier in the blog post as an example.
We then change the Output type of the "properties" array to "Key Value Pivot" as shown below. We are going to set the "Name" node's Output Type to Key, and the "Value" node's Output Type to Value.
Once this has been done, we click on the Output Settings cell next to the "properties" array to open the Key Value Pivot Editor. We then define all the expected Keys which might be returned under the properties array.
Therefore, the final Document Design should be as shown in the screenshot below:
The component will create all the necessary columns based on the Keys we have defined in the Key Value Pivot Editor. We can verify this by opening the Columns page of the component.
Finally, when we execute our SSIS package. We can see that the JSON Source/Extract Component has correctly parsed the Key/Value pair format into a typical tabular format as shown below:
Closing Notes
By working with the new "Key Value Pivot" Output Type in our JSON Source/Extract components, we were able to extract the JSON data, which was in a Key/Value pair format into a typical tabular form. Therefore, it will be easier to integrate our JSON data with any other database system or application. This demonstrates the flexibility of our JSON components when working with different structures of JSON documents that some APIs may use.