JSON SQL Server: Swift Data Integration Methods

Data is your business’s lifeblood. Analyzing business data can help you gain insights to generate reports and create a strategic business plan. If you have large datasets in JSON format, consider migrating them to SQL Server. 

SQL Server enhances data analysis through its orderly storage structure. You can optimize data querying by accessing JSON data stored in tables, rows, and columns. This article will discuss JSON SQL Server migration methods for quickly moving your data.

Let’s look at some methods to load JSON file to SQL Server.

Overview of JSON 

JSON is a formatted data text used to interchange information between varied web applications, servers, or different software. It is easy to read, lightweight, and language-independent. Many programming languages have built-in tools for serializing and deserializing the JSON data, making it easy for developers to format and modify the data. 

Overview of SQL Server 

SQL Server is a database that supports various data types. It allows users to store their data in tables, which is helpful during data querying and manipulation. The database also supports machine learning models and algorithms, which are helpful for predictive analysis. SQL Server database smoothly integrates with Microsoft products to enhance the scalability of your data.

Methods to Migrate Data from JSON to SQL Server

JSON documents sometimes consist of sub-elements or hierarchical data that you can’t easily map into the SQL database. Let’s learn how to load JSON file in SQL Server using different methods.

Method 1: JSON SQL Server Data Migration With Hevo

Hevo is a real-time ELT platform that offers you automated and cost-effective no-code data pipelines. It provides 150+ data sources through which you can export and load data to the destination and transform it for further analysis. Its intuitive user interface makes data transformation easy. Its intuitive user interface makes data transformation easy for technical and non-technical users.

Benefits of Using Hevo 

• Data Transformation: Before conducting a JSON SQL Server data migration, you can cleanse and prepare datasets with Hevo’s Python-based and drag-and-drop transformation features.
• Auto-Schema Mapping: If you are wondering how to read JSON in SQL Server easily, Hevo’s auto-schema data mapping has you covered! This feature automatically checks the data compatibility between your source schema and destination without human intervention.
• Incremental Data Load: Hevo allows you to transfer data from source to destination modified in real-time, optimizing the bandwidth on both ends.
• ELT Pipelines with In-Flight Data Formatting Capabilities: The Hevo data pipeline allows you to access fresh, integrated data through its built-in formatting. 

Let’s see how you can import JSON to SQL Server with Hevo.

Configure REST API to Set JSON as Your Source

To load JSON file to SQL Server using Hevo, you must select a parsing strategy in the last step of creating a pipeline so that the JSON data is easily readable. 

Prerequisites

• The location or the REST API endpoint should be available to load the data. 
• To authenticate your REST API source, you must provide credentials, including your username, password, and API key.
• You should have a basic knowledge of JSONPATH and JSONPATH expressions.
• You should have a Team Administrator, Pipeline Administrator, or Team Collaborator role in Hevo.

Select REST API as Your Source Type 

• Click on PIPELINE in the Navigation Bar. 
• Select +CREATE from the Pipeline View List. 
• On the Select Source Type page, choose REST API as your source. 

To learn more about configuring the source, learn from our documentation page.

Specific the following on the Configure REST API source page-  

1. Specify the name of your pipeline within 255 characters. 
2. Set up your source as REST API. 
3. Specify the data root or path from where you want to replicate the data. 
4. Select a Pagination Method. 
5.  Click on FINISH SETUP.

Construct a Connection for Your REST API Source Endpoint

To set up a connection to your REST API endpoint, you must specify the following:

• Method: There are two methods for requesting data:

1. Get Method (Default): This is an HTTP method for requesting data from your API endpoint. 
2. Post Method: This HTTP method sends data to your REST API source. You must specify the Request Body, including the data sent to your endpoint. The data needs to be present in valid JSON or form data.

• Specify the URL of your REST API source. 
• Specify the authentication method. To learn how to select the authentication method that suits your needs, read the Hevo documentation to specify the type of authentication method.

Set up a Data Replication Path and Select Pagination

• Specify the Data Root

To specify the data replication path, you must select the data root of the JSON PATH expression for the API response element. Hevo suggests a root for the data based on the URL you entered. You can provide a data root or choose from the suggested roots. 

If you enter a data root, check its validity through CHECK PARSED RESPONSE. Then, read the Hevo documentation and specify the root of the data to determine which is best. 

• Select Pagination

Pagination allows you to read your data in smaller parts. To see what you expect, you must check the API response for all Pagination methods except for No Pagination. To set up pagination, correct the pagination method for your data source. Read Set up pagination.

You can refer to the Hevo documentation to learn more about configuring REST API as the source.

Select the SQL Server as Your Destination

Prerequisites

• Your SQL Server should be running. 
• Your host SQL Server’s DNS or IP address should be available. 
• You must grant the critical privileges to your database user. 
• You should have an assigned administrator role, such as a Team Collaborator. 

You must implement a few optional steps before making SQL Server your destination in the data pipeline. 

1. Set up your SQL Server Instance.
2. Allow access to your SQL Server Instance.
3. Create a database for your SQL Server Instance.

You can refer to the Hevo documentation to know more about setting SQL Server as the destination of your data pipeline.

Create a Database User for Your SQL Server

1. Create a login user. 

• Login to your SQL Server instance as a master user through an SQL client tool. 
• Enter the following query to access the particular database where you want to add your user-

USE <database_name>
GO

• Enter the following command to generate a login user-

CREATE LOGIN [<login_name>] WITH PASSWORD=N'<strong_password>', DEFAULT_DATABASE=[<database_name>]
GO

Enter the following command to connect the database to your login user-

CREATE USER [<username>] FOR LOGIN [<login_name>]
GO

Create a Database User for Your SQL Server

• Log in as a login user to your SQL Server instance using the SQL client tool. 
• Execute the following command to access the database that you created for your login user-

USE <database_name>
GO

Execute the following command to grant privileges-

GRANT ALTER, SELECT, INSERT, UPDATE ON SCHEMA::<schema_name> TO <username>
GO
GRANT CREATE TABLE, INSERT, SELECT, UPDATE ON DATABASE::<database_name> to <username>
GO  

Configure your SQL Server Connection Settings

1. Click on the DESTINATION tab from the Navigation bar. 
2. Click on +CREATE on the Destination List View. 
3. On the Add Destination Page, select SQL Server as your destination. 
4. Specify the following fields on the configuration settings page:

• Destination Name: Create a unique name for your destination. 
• Database Host: Select the IP address or DNS for your SQL Server. 
• Database Port: Port where SQL Server instance looks for connection. The default value for this is 1433. 
• Database User: The login user you created or someone with non-administrative privileges. 
• Database Password: Enter the password for the login user. 
• Database Name: Name the database where you want to load the source data. 
• Additional Settings: 

• Connecting through SSH Tunnel: It secures your connection without exposing your SQL Server setup to the public. 
• Sanitize Tables/Columns: This tool allows you to sanitize the table or column names by removing alpha-numeric values. 

• Click on TEST CONNECTION. This is enabled after you specify all the mandatory fields. 
• Click on SAVE & CONTINUE. 

Read more, If you want to know how to configure destination when you export data from SQL Server to JSON file.

Method 2: JSON to SQL Server Data Integration Using OPENROWSET (Bulk)

This method will tell you how to insert JSON data in table in SQL Server.

Prerequisites

• You should know about NVACHAR data type. 
• Must have a basic understanding of JSON and SQL Server. 
• Must be familiar with JSON data, JSON String, JSON Object, and JSON arrays. 
• You should be familiar with built-in JSON functions and operators such as ISJSON(), JSON_VALUE(), and JSON_QUERY(). 

Functionality of OPENROWSET (Bulk) Function

• You can use the OPENROWSET (Bulk) function to read the data from a file. 
• The Bulk function returns data as a JSON table in SQL Server, with a single column containing all the data in the file.
• OPENROWSET (Bulk) can return the entire content of a file in the form of text value.
• The syntax will read content from a file and return the value in BulkColumn.

SELECT * FROM OPENROWSET (BULK ‘file_path’, SINGLE_CLOB) as correlation_name 

Step 1: Inserting a Query 

 We must import data from a JSON file (in this case, named ‘file1.json’) to a SQL Server database table.

Step 2: Importing JSON File to SQL Server

Using the bulk function, we will use the above syntax to read and import the data into the SQL Server.

SELECT * FROM OPENROWSET (BULK 'E:\file1.json', Single_CLOB) AS import;

The output of the above query:

Step 3: Convert JSON Data to a Table

To convert JSON data into a table, you need to:

• Declare a variable for passing the JSON data.
• Store the data you imported into BulkColumn in the variable you created. 
• Use OPENJSON() to convert JSON table to SQL Server table. 
• Use the WITH clause along with the function to define the data parameters.

Here’s the query for the same:

Declare @JSON varchar(max)
SELECT @JSON=BulkColumn
FROM OPENROWSET (BULK 'E:\file1.json', SINGLE_CLOB) import
SELECT * FROM OPENJSON (@JSON)
WITH  (
   [Firstname] varchar(20),  
   [Lastname] varchar(20),  
   [Gender] varchar(20),  
   [AGE] int );

The output for the following query:

Limitations of JSON SQL Server Migration Using OPENROWSET() Function

• Parsing nested JSON arrays using the OPENJSON() function can be time-consuming.
• OPENJSON() supports only limited data types, such as numbers, strings, and booleans. Data types like dates or objects require technical expertise or manual intervention.
• The functionality of an OPENROWSET (Bulk) function depends on the version of SQL Server and impacts operations such as data import/export, data mapping, etc.

JSON SQL Server Use Cases  

• When you save JSON to SQL Server, it facilitates better data storage, data querying, and scalability.
• JSON data files consist of data from different sources, such as APIs and web applications. Inserting JSON table into SQL Server lets you centralize your data, improving its overall performance.

Final Word

JSON SQL Server data integration helps you centralize your source data and perform analysis correctly. Through Hevo’s zero-code data transformation pipeline, you can generate profound insights for your business faster. 

Learn More About how to load data from JSON to Redshift.

FAQs (Frequently Asked Questions)

Q. Can you use SQL queries to manipulate JSON data? 

Many databases today, such as MySQL, SQL Server, and PostgreSQL, have built-in support for JSON data. You can store your JSON data in your favored database and use SQL queries to analyze and manipulate data in your JSON file.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable Hevo pricing that will help you choose the right plan for your business needs.

Visit our Website to Explore Hevo