{"id":621,"date":"2019-08-29T19:32:16","date_gmt":"2019-08-29T14:02:16","guid":{"rendered":"https:\/\/techieshouts.com\/?p=621"},"modified":"2022-08-09T19:06:25","modified_gmt":"2022-08-09T13:36:25","slug":"import-json-files-into-sql-server","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/import-json-files-into-sql-server\/","title":{"rendered":"Import JSON files into SQL Server"},"content":{"rendered":"\n

JavaScript Object Notation (JSON) is a lightweight data-interchange format. The JSON format is widely used to exchange data in most web and mobile applications. Also, the REST web services return results in JSON format. In this article, let’s see how to import JSON files into SQL Server.<\/p>\n\n\n\n

In SQL Server, you can import JSON documents, parse JSON text, transform an array of objects into a table format, and vice versa using built-in functions and operators.<\/p>\n\n\n\n

Like XML, there is no JSON data type to store JSON\ndata in a column. You can use either VARCHAR or NVARCHAR data type.<\/p>\n\n\n\n

Import JSON file as string<\/strong><\/h2>\n\n\n\n

You can use the OPENROWSET<\/a> function to read the data from a file. This function reads the entire file content as a string and returns the value as a table with a single column. The value can be stored in a table or a variable for further processing.<\/p>\n\n\n\n

DECLARE @JSONText VARCHAR(4000)\nSELECT @JSONText = BulkColumn \nFROM OPENROWSET (BULK 'D:\\JSONFiles\\Employees.JSON', SINGLE_CLOB) as TableA\nSELECT @JSONText<\/pre>\n\n\n\n
Use the data\ntype ‘SINGLE_CLOB’ to import the string data. <\/p>\n\n\n\n
\"Import<\/figure><\/div>\n\n\n\n
To validate\nwhether the string is a valid JSON, use ISJSON function. It returns 1 when the\ninput is a valid JSON string.<\/p>\n\n\n\n
IF (ISJSON(@JSONText) = 1)\nBEGIN \nEND<\/pre>\n\n\n\n
Import JSON data into a table<\/strong><\/h2>\n\n\n\n
 You can use OPENJSON<\/a> table-valued function to parse the JSON data and transform it into a tabular format. It returns the objects and properties of the JSON text as rows and columns. <\/p>\n\n\n\n
Method 1: With default schema<\/strong><\/h3>\n\n\n\n
By default, the OPENJSON function returns three columns – key name, value, and the type of each key-value pair.<\/p>\n\n\n\n
DECLARE @JSONText VARCHAR(4000) = N'\n[{\"EmpID\":101,\"FirstName\":\"Sean\",\"MiddleInitial\":\"E\",\"LastName\":\"Paul\"},  \n{\"EmpID\":201,\"FirstName\":\"John\",\"MiddleInitial\":\"R\",\"LastName\":\"Peter\"},  \n{\"EmpID\":301,\"FirstName\":\"Samantha\", \"MiddleInitial\":null, \"LastName\":\"Sam\"}]'\nSELECT * FROM OPENJSON(@JSONText)<\/pre>\n\n\n\n
\"Import
OPENJSON with default schema<\/figcaption><\/figure><\/div>\n\n\n\n
Type column in the above image returns a number that denotes the data type of the value column. It ranges from 0 through 5 for various data types.<\/p>\n\n\n\n
\"JSON<\/figure><\/div>\n\n\n\n
Method 2: With explicit schema definition<\/strong><\/h3>\n\n\n\n
As an alternative to the default method, you can define the schema of the result by using with clause. This method allows you to specify the column name and its data type. <\/p>\n\n\n\n
DECLARE @JSONText VARCHAR(4000) = N'\n[{\"EmpID\":101,\"FirstName\":\"Sean\",\"MiddleInitial\":\"E\",\"LastName\":\"Paul\"},  \n{\"EmpID\":201,\"FirstName\":\"John\",\"MiddleInitial\":\"R\",\"LastName\":\"Peter\"},  \n{\"EmpID\":301,\"FirstName\":\"Samantha\", \"MiddleInitial\":null, \"LastName\":\"Sam\"}]'\nSELECT * FROM OPENJSON(@JSONText)\nWITH(EmpID INT, FirstName VARCHAR(25), MiddleInitial CHAR(1), LastName VARCHAR(25))<\/pre>\n\n\n\n
\"Import
OPENJSON with an explicit schema definition<\/figcaption><\/figure>\n\n\n\n
Below is the consolidated script to import, parse and validate JSON files.<\/p>\n\n\n\n
DECLARE @JSONText VARCHAR(4000)\n--OPENROWSET method to read JSON file\nSELECT @JSONText = BulkColumn FROM OPENROWSET (BULK 'D:\\JSONFiles\\Employees.JSON', SINGLE_CLOB) AS TableA\nSELECT @JSONText\n--Validate JSON\nIF (ISJSON(@JSONText) = 1)\nBEGIN \n--OPENJSON Default Schema\nSELECT * FROM OPENJSON(@JSONText)\n--OPENJSON Explicit Schema\nSELECT * FROM OPENJSON(@JSONText)\nWITH(EmpID INT, FirstName VARCHAR(25), MiddleInitial CHAR(1), LastName VARCHAR(25))\nEND<\/pre>\n","protected":false},"excerpt":{"rendered":"
JavaScript Object Notation (JSON) is a lightweight data-interchange format. The JSON format is widely used to exchange data in most web and mobile applications. Also, the REST web services return results in JSON format. In this article, let’s see how to import JSON files into SQL Server. In SQL Server, you can import JSON documents,\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[10,15],"tags":[59,222,60,58,39,40,41],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/621"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=621"}],"version-history":[{"count":6,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/621\/revisions"}],"predecessor-version":[{"id":1076,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/621\/revisions\/1076"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}