Open In App

How to Import JSON Data into SQL Server?

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

JSON refers to Javascript Object Notation. It is a popular text data format used to exchange data on modern web and mobile applications. It is based on a subset of the Javascript programming language. It is used to store random data in log files or NoSQL.

It also enables us to integrate NoSQL and related concepts into the same database. 

Advantage of  JSON data:

  • Lightweight data-interchange format.
  • Easy for humans to read and write.
  • Easy for machines to parse and generate.

 JSON data using built-in functions and operators:

  • We can parse the JSON text and read or modify the values.
  • Transform JSON objects into table format.
  • We can run any Transact-SQL query on the converted JSON objects.
  • Transform the results of Transact-SQL queries back to JSON format.
Function Description
ISJSON  It tests whether a string contains JSON data or not
JSON_VALUE  It extracts a scalar value from a JSON string.
JSON_QUERY  It extracts an object or an array from a JSON string.
JSON_MODIFY  It changes a value in a JSON string.

 

Now, to import JSON data in the SQL server, we will use OPENROWSET (BULK). 

Step 1: Use of OPENROWSET(BULK) 

It is a table-valued function that can read data from any file.. It returns a table with a single column that contains all the contents of the file. It can just load the entire contents of a file as a text value. (This single large value is known as a single character large object or SINGLE_CLOB.)

Syntax: 

SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name;

It reads the content of the file and returns it in BulkColumn. The correlation name must be specified. We have the JSON file named “file1.json”. Content of JSON file:

Query:

[{"Firstname": "ROMY", "Lastname": "KUMARI", "Gender": "female", "AGE" : 22 },
{"Firstname": "PUSHKAR", "Lastname": "JHA", "Gender": "male", "AGE" : 22 },
{"Firstname": "SHALINI", "Lastname": "JHA", "Gender": "female", "AGE" : 21 },
{"Firstname": "SAMBHAVI", "Lastname": "JHA", "Gender": "female", "AGE" : 18 } ]

Step 2: Import file1.json into SQL server

Query:

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

Output:

The entire content is returned as a single column.

Step 3: Convert JSON data

  • Declare a variable.
  • Store data of Bulkcolumn in a variable.
  • Use OPENJSON() function for converting the JSON output from a variable into a tabular format.
  • WITH clause along with the column definition (key should be used as column name).

Query:

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

Output:


Last Updated : 23 Sep, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads