Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Import JSON Data into SQL Server?

  • Last Updated : 23 Sep, 2021

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. 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

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.
FunctionDescription
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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!