17 September,2019 by Tom Collins
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |