How to load complex nested JSON data from JIRA API into SQL Server database using Azure Data Factor?

Question

Hello,

I am currently working on a project where I need to load complex JSON data from the JIRA API into a SQL Server database table. I have created a table with a single column named jsonData, and I have tried setting the data type to both VARCHAR(MAX) and NVARCHAR(MAX). My approach involves using the Copy activity to load all data from the JIRA API source into this single column.

In the mapping configuration, I have set the source path to "$." and mapped it to the jsonData field in the destination table JIRA_Data. However, I am encountering the following error:

EErrorCode=DataTypeNotSupported,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column: jsonData,The data type varchar is not supported for the column named jsonData.,Source=,.

This error occurs regardless of whether I use VARCHAR or NVARCHAR as the data type for the jsonData column.

My goal is to initially load the JSON data into this single column and then parse it using SQL to distribute the data into different columns. I am uncertain whether this approach is optimal or if it would be better to flatten the JSON data and load it directly into separate fields.

Could you please provide guidance on how to successfully load the JIRA data into the SQL Server database table? Any advice on best practices for handling this type of data would be greatly appreciated.

Thank you for your assistance.

Answer 1

Thank you for your answer and here is my response to those:

1. Data Type Compatibility - The error you're encountering indicates that the VARCHAR data type is not supported for the jsonData column. Instead, you should use the NVARCHAR(MAX) type, which is generally more compatible with JSON data in SQL Server. Answer: This error occurs regardless of whether I use VARCHAR or NVARCHAR as the data type for the jsonData column.
2. Flattening JSON Data - While you can load the JSON data into a single column, it is often more efficient to flatten the JSON data and load it directly into separate fields. This approach allows for easier querying and manipulation of the data later on. Answer: However, I am unsure how to maintain the table structure due to the variability in the number of arrayed values. For instance, the STATE value in the JSON data can have one or multiple entries. Consequently, the table structure may vary based on this number. Alternatively, the column could store the JSON value as-is, which would eliminate the need to determine the number of states, as the JSON data itself indicates this information.
3. Using Copy Activity - When configuring the Copy activity, ensure that the mapping is correctly set up to handle the JSON structure. You may need to adjust the source path in your mapping configuration to accurately reflect the structure of the JSON data you are retrieving from the JIRA API. Answer: I am indicating that the structure varies depending on the data, which complicates the process.
4. Error Handling - If you continue to face issues, check the specific error messages returned by the Copy activity for further insights. You may also want to review the settings in the Copy activity, particularly focusing on the data types and mappings. Answer: The table structure may vary according to the array data, which might be an issue you have encountered before. How do you typically resolve this, aside from storing the entire data as JSON in a single column and then parsing it using SQL?
5. Testing with Sample Data - Before executing the full load, consider testing with a smaller subset of your JSON data to ensure that your configuration works as expected. Answer : Something I am trying to work on but no results. sadly. :-(

Hope I answered all your options!!! Sorry did not help!