In this article, we will learn how to parse and query JSON in SQL Server with the help of the OPENJSON function. Firstly, we will briefly look at the data structure of the JSON and then we will learn details of the parsing and querying JSON data using the OPENJSON function.
What is JSON?
JSON (Java Script Object Notation) is a lightweight text-based data format that helps to interchange data easily between modern applications. At the same time, JSON is used by the NoSQL (Microsoft Azure Cosmos DB, CouchDB, etc.) databases to store the unstructured data. The small data size is the main feature of the JSON and this property enables to an interchange of the data easily between the applications. At the same time, JSON offers the following advantages:
- The JSON data structure is easily readable by humans and machines
- JSON has a compact data structure and it does not include unnecessary data notations
- JSON has extensive usage. All modern programming languages and application platforms support working with JSON
- JSON has a straightforward syntax
The JSON data structure is essentially based on a key-value pair format. The keys must be string data type and the values data types must be in JSON data type. JSON supports the following data types :
- string
- number
- boolean
- null
- object
- array
At the same time, a JSON object can contain two different data structures:
- An object type can contain multiple key-value pairs inside the JSON text
- An array can contain single or multiple values
The following JSON represents some attributes of a car brand. The color attribute represents an array and the Model attribute represents an object in this JSON object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
{ "owner": null, "brand": "BMW", "year": 2020, "status": false, "color": [ "red", "white", "yellow" ], "Model": { "name": "BMW M4", "Fuel Type": "Petrol", "TransmissionType": "Automatic", "Turbo Charger": "true", "Number of Cylinder": 4 } } |
OPENJSON() function parses JSON in SQL Server
As we mentioned in the previous section, JSON is used widely by the applications. In this context, the conversion of the JSON data into the relational format is becoming more important.
OPENJSON is a table-valued function that helps to parse JSON in SQL Server and it returns the data values and types of the JSON text in a table format. Now, we will look at the syntax of this function.
|
1 2 3 4 5 |
OPENJSON( jsonExpression [, jsonPath ] ) [ WITH (column_mapping_definition1 [,column_mapping_definition2] [,… column_mapping_definitionN]) ] |
The jsonExpression is an input parameter that specifies the JSON text that will be parsed by the OPENJSON function.
The jsonPath is an optional parameter and it is used to seek a specified JSON expression in the JSON text and the OPENJSON function parses only this part of the JSON text.
The WITH clause is an optional parameter that can be used to explicitly specifies the schema.
In the following example, we can see a very simple usage of the OPENJSON function. It will parse JSON in SQL Server and return the parsed JSON in the default schema format.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @json NVarChar(2048) = N'{ "owner": null, "brand": "BMW", "year": 2020, "status": false, "color": [ "red", "white", "yellow" ], "Model": { "name": "BMW M4", "Fuel Type": "Petrol", "TransmissionType": "Automatic", "Turbo Charger": "true", "Number of Cylinder": 4 } }'; SELECT * FROM OpenJson(@json); |
In this result set, we can observe that the OPENJSON function is executed with the default schema and it has returned three columns:
- The key column indicates the name of the key
- The value column shows the value of the key
- The type column indicates the data types of the key column through the numbers. The following table illustrates the possible values of the type column and their data type explanations
|
Type column |
JSON data type |
|
0 |
null |
|
1 |
string |
|
2 |
int |
|
3 |
true/false |
|
4 |
array |
|
5 |
object |
- Tip: OPENJSON function does not work in the databases with compatibility level less than 130 and returns the following error. So we can not use the OPENJSON function to parse JSON in SQL Server
Using OPENJSON with explicit schema
The returning columns of the JSON result set can be defined by the users. In this usage method, we need to specify output columns and their types and then we can pass this user-defined schema to OPENJSON through WITH keyword.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @json NVarChar(2048) = N'{ "brand": "BMW", "year": 2019, "price": 1234.6, "color": "red", "owner": null }' SELECT * FROM OpenJson(@json) WITH (CarBrand VARCHAR(100) '$.brand', CarModel INT '$.year', CarPrice MONEY '$.price', CarColor VARCHAR(100) '$.color', CarOwner NVARCHAR(200) '$.owner' ) |
We need to use AS JSON keyword to specify JSON objects or arrays which are contained in the JSON text. In addition, the column data type must be NVARCHAR(MAX).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @json NVarChar(2048) = N'{ "owner": null, "brand": "BMW", "year": 2020, "status": false, "color": [ "red", "white", "yellow" ], "Model": { "name": "BMW M4", "Fuel Type": "Petrol", "TransmissionType": "Automatic", "Turbo Charger": "true", "Number of Cylinder": 4 } }'; SELECT * FROM OpenJson(@json) WITH (CarOwner NVARCHAR(200) '$.owner', CarBrand NVARCHAR(200) '$.brand', CarModel INT '$.year', CarPrice BIT '$.status', CarColor NVARCHAR(MAX) '$.color' AS JSON, CarColor NVARCHAR(MAX) '$.Model' AS JSON ) |
How to parse the JSON arrays with OPENJSON?
JSON arrays are used to store multiple key values. The JSON arrays are specified with square brackets ([ ]) in the JSON text and values are separated with a comma. When we parse a JSON text which includes an array through OPENJSON, the result set will look like as below.
|
1 2 3 4 5 6 7 |
DECLARE @json NVarChar(max)='{ "LoginName" : "SystemLogin", "Authenticationtype" : "Windows", "Roles" : [ "bulkadmin", "setupadmin", "diskadmin" ] }' select * from OPENJSON(@json) |
As we can see, the OPENJSON function has parsed the root level objects but the array has returned as JSON text. When we want to convert this JSON array data into the relational format, we need to use the OPENJSON function again and join to the root level table.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @json NVarChar(max)='{ "LoginName" : "SystemLogin", "Authenticationtype" : "Windows", "Roles":[ "bulkadmin", "setupadmin", "diskadmin" ]}' select LoginName,AuType,RolesJson,RoleName from OPENJSON(@json) WITH( LoginName VARCHAR(20) '$.LoginName' , AuType VARCHAR(20) '$.Authenticationtype', RolesJson nvarchar(MAX) '$.Roles' AS JSON) CROSS APPLY OPENJSON(RolesJson) WITH ( RoleName VARCHAR(20) '$') |
In another way, we can use the jsonPath parameter to covert the JSON array into a relational format.
|
1 2 3 4 5 6 7 8 |
DECLARE @json NVarChar(max)='{ "LoginName" : "SystemLogin", "Authenticationtype" : "Windows", "Roles":[ "bulkadmin", "setupadmin", "diskadmin" ]}' select *from OPENJSON(@json,'$.Roles') WITH( RoleName VARCHAR(20) '$' ) |
- Tip: The lax and strict are the two JSON path mode. In the lax mode, if the specified JSON path expression does not find in the JSON text it does not return an error and it is the default path mode. For example, there is not any Unknown attribute in the JSON text but the OPENJSON function does not return any error
|
1 2 3 4 5 6 7 8 |
DECLARE @json NVarChar(max)='{ "LoginName" : "SystemLogin", "Authenticationtype" : "Windows", "Roles":[ "bulkadmin", "setupadmin", "diskadmin" ]}' select *from OPENJSON(@json,'$.Unknown') WITH( RoleName VARCHAR(20) '$' ) |
In the strict mode, if the specified JSON path expression does not find in the JSON text it returns an error.
|
1 2 3 4 5 6 7 8 |
DECLARE @json NVarChar(max)='{ "LoginName" : "SystemLogin", "Authenticationtype" : "Windows", "Roles":[ "bulkadmin", "setupadmin", "diskadmin" ]}' select *from OPENJSON(@json,'strict $.Unknown') WITH( RoleName VARCHAR(20) '$' ) |
How to parse a JSON file with OPENJSON?
OPENROWSET function is used to read data from files from the file system. The below query will read the JSON file contents in the specified file path.
|
1 2 |
SELECT * FROM OPENROWSET (BULK 'C:\sample-json-file.json', SINGLE_CLOB) as JsonFile |
Now, we will convert the JSON data to the relational format with the help of the OPENJSON function.
|
1 2 3 |
SELECT [value] ,[type],[key] FROM OPENROWSET (BULK 'C:\sample-json-file.json', SINGLE_CLOB) as JsonFile CROSS APPLY OPENJSON(BulkColumn) |
How to call JSON API in SQL Server?
The JSON API enables us to communicate between two application platforms in the JSON data format. These API’s results are returns in the JSON format and accept the data that’s formatted as JSON. Such as, this JSON API returns weather data information for London.
In order to communicate any JSON API from the SQL Server, we can use OLE Automation Stored Procedures. These procedures provides to access OLE or COM objects directly over SQL Server. By default OLE Automation Stored Procedures are disabled and we need to enable them.
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE; GO |
The following query will get weather information result from the HTTP server and then convert the JSON text into the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Declare @WinHttpObject as Int; Declare @ResponseJsonText as Varchar(8000); Exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @WinHttpObject OUT; Exec sp_OAMethod @WinHttpObject, 'open', NULL, 'get', 'https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=439d4b804bc8187953eb36d2a8c26a02', 'false' Exec sp_OAMethod @WinHttpObject, 'send' Exec sp_OAMethod @WinHttpObject, 'responseText', @ResponseJsonText OUTPUT Exec sp_OADestroy @WinHttpObject IF ISJSON(@ResponseJsonText)=1 BEGIN SELECT City,Weather FROM OPENJSON(@ResponseJsonText) WITH(City VARCHAR(100) '$.name') CROSS APPLY OPENJSON(@ResponseJsonText,'$.weather') WITH(Weather VARCHAR(100) '$.description') END |
Now, we will explain the above query line by line.
|
1 2 3 |
Declare @WinHttpObject as Int; Declare @ResponseJsonText as Varchar(8000); Exec sp_OACreate 'WinHttp.WinHttpRequest.5.1', @WinHttpObject OUT; |
As a first step, we declared the required variables and create an instance of the WinHTTP object with the help of the sp_OACreate procedure.
|
1 2 3 4 5 |
Exec sp_OAMethod @WinHttpObject, 'open', NULL, 'get', 'https://samples.openweathermap.org/data/2.5/weather?q=London,uk&appid=439d4b804bc8187953eb36d2a8c26a02','false' Exec sp_OAMethod @WinHttpObject, 'send' Exec sp_OAMethod @WinHttpObject, 'responseText', @ResponseJsonText OUTPUT Exec sp_OADestroy @WinHttpObject |
In this part of the query sp_OAMethod procedure opened an HTTP connection and sent an HTTP request to the server. ResponseText method retrieved the response of the web server as a text. As a last, the sp_OADestroy procedure destroyed the created instance of the object.
|
1 |
IF ISJSON(@ResponseJsonText)=1 |
Before parsing the HTTP server result, we checked that the response text is a valid JSON format. The ISJSON function controls whether an expression is valid JSON text. It returns 1 if the input expression is a valid JSON otherwise it returns 0.
In the final part of the query, we used the OPENJSON function to parse the returned JSON text. The city name is stored in the name attribute so we defined a schema to obtain this value. The second schema parsed the weather array and got the description value from this array.
|
1 2 3 4 5 6 |
BEGIN SELECT City, Weather FROM OPENJSON(@ResponseJsonText) WITH(City VARCHAR(100) '$.name') CROSS APPLY OPENJSON(@ResponseJsonText,'$.weather') WITH(Weather VARCHAR(100) '$.description') END |
Conclusion
In this article, we explored the JSON notion and learned the data structure of this object. We also explored parse JSON in SQL Server through the OPENJSON function. JSON is used by modern applications to exchange data and store unstructured data. On the other hand, relational databases are an indispensable part of our software development life cycle. .JSON is not an alternative for the relational database model but it has a schema-less data structure that makes it more flexible to store unstructured data and combining this data model to relational data called a hybrid data model. All these features help in storing and processing both relational and JSON data by using T-SQL and this hybrid model opens a gate of the new data age.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023