How to import JSON file into a SQL recordset with OPENJSON

17 September,2019 by Jack Vamvas

Question: I have a JSON file loaded daily onto a drive . I need to set up a process to read the JSON file and translate into a SQL  recordset. Eventually - it will be added to a SQL table. Is it possible to read a simple JSON file using T_SQL ?

Answer: It is possible to read a json file using T-SQL.There are a number of different methods.  By using the OPENROWSET functionality , ISJSON and OPENJSON function you can quickly read the file , check if the JSON is valid and then unpack the JSON into a SQL table. 

This is an example of importing a simple json file. 

This is the content of the json file :

{"Name":"Joe","Surname":"Satriani","Age":55,"Music skills":["Rock Guitar","Bass Guitar"]}

 

Step 1 : Declare a variable

DECLARE @JSON VARCHAR(MAX)

Step 2 : Use OPENROWSET , for an adhoc access to remote data 

SELECT @JSON = BulkColumn
FROM OPENROWSET
(BULK 'E:\temp\guitar_players.json', SINGLE_CLOB)
AS j

 

Step 3 : Check if the data is valid. Use the ISJON function

SELECT ISJSON(@JSON)

If (ISJSON(@JSON)=1)
SELECT @JSON AS 'My JSON String'

 

Step 4:Select out into a SQL Table using the OPENJSON function 

SELECT * FROM OPENJSON(@JSON);

based on the  json file example - this is the output of the SELECT statement 

key value type
Name Joe 1
Surname Satriani 1
Age 55 2
Music skills ["Rock Guitar","Bass Guitar"] 4

 

Once you have the recordset in this state - there is then tons of flexibility using T-SQL to manipulate the data according the task requirements . 

If you already using JSON let me know what method you use


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to import JSON file into a SQL recordset with OPENJSON


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer