In this article, we will explain what JSON is, what are the SQL Server JSON functions introduced in 2016, how to index JSON values, and how to import JSON data into SQL tables.
Introduction
JavaScript Object Notation (JSON) is an open universal data format that uses human-readable text to store data objects in a map format (Key/value pairs). This object notation was standardized in 2013 as ECMA-404. Many years ago, JSON was not popular, while XML was widely used in the data exchange operations. With the rise of the Big Data and NoSQL notions, JSON started slowly to replace XML since it is used and supported by the new data technologies.
Since companies and data consumers widely use traditional database management systems, these systems’ providers started supporting the new data technologies and features to keep pace with their customers emerging needs.
Regarding SQL Server, starting with the 2012 release, the database engine is not considered anymore for medium scale enterprises after adding high-end data-center management capabilities. In the 2016 version, Polybase was introduced, adding the ability to connect traditional databases with NoSQL databases and Hadoop data lakes (more capabilities were introduced in 2019 such as MongoDB and Oracle database support). Besides, JSON becomes supported, enabling developers to combine NoSQL and relational concepts by storing documents formatted as JSON text within tables storing traditional data.
In the following sections, we will explain SQL Server JSON functions and how to index JSON values and import JSON data into SQL.
SQL Server JSON functions
Based on the official Microsoft documentation, the added JSON functionalities allows developers to:
- Parse JSON text and read or modify values
- Transform arrays of JSON objects into table format
- Run any Transact-SQL query on the converted JSON objects
- Format the results of Transact-SQL queries in JSON format
In this section, we will explain each functionality added by providing some examples using the AdventureWorks database. Before describing these functionalities, we will create a new field of type NVARCHAR(MAX) within the [Person].[Person] table to be used during this guide.
FOR JSON clause
This clause is used to convert tabular data into a JSON text. There are two options to be used within this clause:
- FOR JSON PATH: to format and configure the output JSON text manually
- FOR JSON AUTO: to format the output JSON text automatically
In this guide, we will use the second option to generate JSON text. As an example, let’s convert a record from the Person table to a JSON text:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT TOP 1 [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[Person].[Person] FOR JSON AUTO |
The result will be shown as following:
Figure 1 – Using FOR JSON AUTO to generate JSON text
To continue our experiments, we will fill the newly added NVARCHAR(MAX) column (called Details) with the JSON text created from the other columns using the following query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
UPDATE [AdventureWorks2017].[Person].[Person] SET [Details] = (SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[Person].[Person] T1 WHERE T1.[BusinessEntityID] = [AdventureWorks2017].[Person].[Person].[BusinessEntityID] FOR JSON AUTO) |
If you decided to go with the first option we mentioned (FOR JSON PATH), you can refer to the following official documentation.
ISJSON() function
ISJSON() is the simplest SQL Server JSON function; it checks if a given string is a valid JSON text. It returns 1 if the string is valid, else it returns 0.
As an example, we will use check if FirstName and Details (the added NVARCHAR(MAX) column) columns contain a valid JSON text:
|
1 2 |
SELECT TOP 1 ISJSON([FirstName]) IsJson_FirstName,ISJSON([Details]) IsJson_Details FROM [AdventureWorks2017].[Person].[Person] |
As shown in the image below, FirstName does not contain a valid JSON while Details does.
Figure 2 – Using ISJSON() function to validate columns
JSON_VALUE() function
To extract a scalar value from a JSON text, we can use JSON_VALUE() function. This SQL Server JSON function takes the input JSON string and the JSON path that contains the property to extract as arguments. (Note that the context item of a JSON path is a dollar sign ($).)
In the following example, we used JSON_VALUE() function to filter the query result where the BusinessEntityID value stored within the first array of the JSON text is equal to 1:
|
1 2 3 |
SELECT * FROM [AdventureWorks2017].[Person].[Person] WHERE JSON_VALUE([Details], '$[0].BusinessEntityID') = '1' |
As shown below, the query returns only one row:
Figure 3 – Using JSON_VALUE() function to filter a query result
More examples of the JSON_VALUE() function can be found in the official documentation.
Besides, if you are not familiar with JSON PATH expressions, you can refer to the following article.
JSON_QUERY() function
To extract an array or object from a JSON text, we must use JSON_QUERY() function. This SQL Server JSON function is similar to JSON_VALUE(); it takes the same arguments (input JSON and path) and returns a JSON text (NVARCHAR(MAX)) value.
As an example, let’s return the items stored within the first array in the root element:
|
1 2 |
SELECT TOP 5 JSON_Query([Details], '$[0]') FROM [AdventureWorks2017].[Person].[Person] |
As shown in the image below, the result is a JSON string.
Figure 4 – Using JSON_QUERY() to extract JSON objects
JSON_MODIFY() function
This function modifies the value of a property within a JSON string and returns the updated JSON text. It takes three parameters; the input JSON string, the property JSON path, and the new value. As an example, If we are looking to add a leading zero to the BusinessEntityID property stored within the Details column. We should use the following query:
|
1 2 |
UPDATE [AdventureWorks2017].[Person].[Person] SET [Details] = JSON_MODIFY([Details],'$[0].BusinessEntityID','0' + JSON_VALUE([Details], '$[0].BusinessEntityID')) |
Now if we execute the query we provided in the JSON_VALUE() function section, it will not retun any result until we add a leading zero to the condition:
|
1 2 3 |
SELECT * FROM [AdventureWorks2017].[Person].[Person] WHERE JSON_VALUE([Details], '$[0].BusinessEntityID') = '01' |
The following image shows the result of this query:
Figure 5 – Query result after modifying the JSON text value
OPENJSON() function
If you are looking to parse a JSON text into a key-value pairs or a tabular data, you can use OPENJSON() table-valued function. To convert a JSON string to a key-value pair we must use OPENJSON() function as follows:
|
1 2 3 |
DECLARE @json nvarchar(max) SELECT @json = JSON_QUERY([Details],'$[0]') from [AdventureWorks2017].[Person].[Person] WHERE JSON_VALUE([Details], '$[0].BusinessEntityID') = '01' SELECT * FROM OPENJSON(@json) |
The result of this query is a key-value pair of all JSON array objects in addition to the value type:
Figure 6 – Using OPENJSON() to retrun a jey-value pairs
The following JSON value types are supported:
Type code | Type description |
0 | null |
1 | string |
2 | int |
3 | true/false |
4 | array |
5 | object |
If we know the JSON text schema, we can use OPENJSON() function to convert JSON into tabular data. We need to define the schema explicitly after this SQL Server JSON function. As an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @json nvarchar(max) SELECT @json = [Details] from [AdventureWorks2017].[Person].[Person] WHERE JSON_VALUE([Details], '$[0].BusinessEntityID') = '01' SELECT * FROM OPENJSON(@json) WITH ([BusinessEntityID] [int] , [PersonType] [nchar](2) , [NameStyle] [dbo].[NameStyle] , [Title] [nvarchar](8) , [FirstName] [dbo].[Name] , [MiddleName] [dbo].[Name] , [LastName] [dbo].[Name] , [Suffix] [nvarchar](10) , [EmailPromotion] [int] , [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) , [Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) , [rowguid] [uniqueidentifier] , [ModifiedDate] [datetime] ) |
As shown in the image below, the query result is in tabular form.
Figure 7 – Using OPENJSON() function with explicit schema
Indexing JSON column
If we need to filter our queries based on a property value within the JSON column, and we need that this property is indexed, we should add a computed column based on this value. Then we must create an index for this column. As an example:
|
1 2 3 4 5 |
ALTER TABLE Person.Person ADD vBusinessEntityID AS JSON_VALUE(Details,'$[0].BusinessEntityID') CREATE INDEX idx_BusinessEntityID ON Person.Person(vBusinessEntityID) |
Import JSON data into SQL database
There are many methods to import JSON data into SQL database:
- Using Integration Services
- Using OPENROWSET() with OPENJSON() function
These methods are described in detail in this article, Import JSON data into SQL Server.
Conclusion
In this article, we have illustrated the SQL Server JSON functions added with the 2016 version. We provided some examples using the AdventureWorks database. We explained how to index properties stored within the JSON column, and finally, we briefly noted the available methods to import JSON into the SQL database.
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023