First published on MSDN on Oct 07, 2015
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:
-
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.
-
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.
-
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.
-
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