How to import JSON file into a SQL recordset with OPENJSON

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