Importing JSON files into SQL Server using OPENROWSET (BULK)

Importing JSON files in SQL Server

Currently you can find many JSON documents stored in files. Sensors generate information that are stored in files, applications log information in JSON files, etc. One important thing that you would need to do is to read JSON data stored in files, load them in SQL Server, and analyze them.

In this post we will see how you can import JSON files in SQL Server.

Importing files using OPENROWSET(BULK)

OPENROWSET (BULK) is a table value function that can read data from any file on the local drive or network if Sql Server has read access to that location. It returns a table with a single column (i.e. BulkColumn) that contains content of the file. In general case you can use various options with OPENROWSET (BULK) function, such as separators etc., but in the simplest case you can directly load entire content of a file as a text value (or single character large object a.k.a. SINGLE_CLOB in OPENROWSET “terminology”) and load content of that cell in any table or variable. An example of OPENROWSET (BULK) function that reads content of JSON file and return it to user as a query result is shown in the following example:

SELECT BulkColumn
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

OPENJSON( BULK) will read content of the file and return it via BulkColumn. You can also load content of file in some local variable or table, as it is shown in the following example:

SELECT @json = BulkColumn
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j
 
 SELECT BulkColumn
 INTO #temp
 FROM OPENROWSET (BULK 'C:\JSON\Books\book.json', SINGLE_CLOB) as j

 

Importing JSON content

In this post I will show you how to import content of JSON file directly into SQL Server tables using OPENROWSET(BULK). I will use the fact that JSON is regular text so it can be imported as any other text format. Therefore, I can leverage existing functions that work with text and in this case import text from a file. In this example I will use JSON file containing books taken from this site. Instead of reading entire JSON text, I want to parse it and return either books in the file, or their properties.

In the simplest example we can read JSON objects from the file:

SELECT value
 FROM OPENROWSET (BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) as j
 CROSS APPLY OPENJSON(BulkColumn)

OPENROWSET will read single text value from a file, return it as a BulkColumn, and pass it to the applied OPENJSON function. OPENJSON will iterate through the array of JSON objects in the BulkColumn array and return one book formatted as JSON in each row:

Value
{“id” : “978-0641723445″,”cat” : [“book”,”hardcover”],”name” : “The Lightning Thief”, …
{“id” : “978-1423103349″,”cat” : [“book”,”paperback”],”name” : “The Sea of Monsters”, …
{“id” : “978-1857995879″,”cat” : [“book”,”paperback”],”name” : “Sophie’s World : The Greek …
{“id” : “978-1933988177″,”cat” : [“book”,”paperback”],”name” : “Lucene in Action, Second …

 

With new OPENJSON function we can parse that JSON content and transform it to a table or result set. In this example we will load content, parse loaded JSON and return five fields as columns:

SELECT book.*
 FROM OPENROWSET (BULK 'C:\JSON\Books\books.json', SINGLE_CLOB) as j
 CROSS APPLY OPENJSON(BulkColumn)
 WITH( id nvarchar(100), name nvarchar(100), price float,
 pages_i int, author nvarchar(100)) AS book

OPENROWSET(BULK) will read content of the file and we can pass that content to OPENJSON function with defined schema. OPENJSON will match properties in JSON objects using column names (e.g. price property will be returned as a price column and converted to float type). Results in this case would look like:

Id Name price pages_i Author
978-0641723445 The Lightning Thief 12.5 384 Rick Riordan
978-1423103349 The Sea of Monsters 6.49 304 Rick Riordan
978-1857995879 Sophie’s World : The Greek Philosophers 3.07 64 Jostein Gaarder
978-1933988177 Lucene in Action, Second Edition 30.5 475 Michael McCandless

 

Now we can either return this table to the user, or load it into another table.

Loading data from Azure File Storage

You can use the same approach to read JSON files from any file that can be accessed by SQL Server. As an example, Azure File Storage supports SMB protocol, so you can map your local virtual drive to the Azure File storage share using the following procedure:

  1. Create file storage account (e.g. mystorage), file share (e.g. sharejson), and folder using Azure portal or Azure PowerShell SDK. Upload some JSON file in the file storage share.
  2. Create firewall outbound rule in Windows Firewall on your computer that allows port 445. Note that this port might be blocked by your internet provider. If you are getting DNS error (error 53) in the following step then you have not opened that port or it is blocked by your ISP.
  3. Mount Azure File Storage share as local drive (e.g. t: ) using the following command:

net use [drive letter] \\[storage name].file.core.windows.net\[share name] /u:[storage account name] [storage account access key]

Example that I have used is:

net use t: \\mystorage.file.core.windows.net\sharejson /u:myaccont hb5qy6eXLqIdBj0LvGMHdrTiygkjhHDvWjUZg3Gu7bubKLg==

Storage account key and primary or secondary storage account access key can be found in the Keys section in Settings on Azure portal.

  1. Now you should be able to access your JSON file via share name, e.g.:

SELECT book.* FROM

OPENROWSET(BULK N’t:\books\books.json’, SINGLE_CLOB) AS json

CROSS APPLY OPENJSON(BulkColumn)

WITH( id nvarchar(100), name nvarchar(100), price float,

pages_i int, author nvarchar(100)) AS book

 

Loading set of files into SQL Server

We can use the same approach to load a set of JSON files from the file system into local variables (we will assume that files are named as book<index>.json):

declare @i int = 1
 
 declare @json AS nvarchar(MAX)
 
 while(@i<10)
 
 begin
 
 SET @file = 'C:\JSON\Books\book' + cast(@i as varchar(5)) + '.json';
 
 SELECT @json = BulkColumn FROM OPENROWSET (BULK (@file), SINGLE_CLOB) as j
 
 SELECT * FROM OPENJSON(@json) as json
 
 set @i = @i +1;
 
 end

As you can see, with OPENROWSET(BULK) function you can easily import files into SQL Server.