MS SQL Server

SQL Server JSON Data Type

JavaScript Object Notation or JSON for short, is a data interchange format used in storing and transporting data. It is heavily utilized in applications such as document databases which use documents indexes instead of tables. It is also the standard language for API endpoints for operations such as ingestion and results.

In SQL, we can work with JSON data and convert it into rows and columns are we will see in this tutorial.

The Basics

Json data is organized into sets of unique keys and their respective pairs. You can also create multiple records using curly braces.

The following string shows an example of a valid JSON:

{
        "id": 1,
        "firstname": "Rosanne",
        "lastname": "Carbo",
        "date_of_birth": "1987-10-06",
        "city": "Vladivostok",
        "country": "Turkmenistan",
        "countryCode": "AU",
        "email": "[email protected]",
        "height": 19.957,
        "is_employed": true,
        "salary": "$120000",
        "department": "Database Developer"
    }

Using various methods and functions, we can validate JSON data and transform it into a SQL table.

SQL Server Validate JSON

SQL Server provides you with a function to determine if the provided string is a valid JSON data or not.

The syntax is as shown:

ISJSON ( expression )

Where the expression refers to the actual JSON to string to test for validity.

[email protected] nvarchar(max)
set @var = '
    {
        "id": 1,
        "firstname": "Rosanne",
        "lastname": "Carbo",
  ... ... truncated ... ...
        "salary": "$120000",
        "department": "Database Developer"
    }
'

if (isjson(@var) > 0)
begin
    print 'Valid JSON'
end

The query should test if the provided string is a valid JSON data and return an integer. 1 indicates the string is valid json and 0 if otherwise.

The above query should return:

Valid JSON

SQL Server Modify JSON Data

SQL Server also allows you to modify data that is stored in a JSON string. Using the JSON_MODIFY function, you can change the value of a specific JSON property and return the update JSON data.

For example:

declare @var nvarchar(max)
set @var = '
    {
        "id": 1,
        "firstname": "Rosanne",
        "lastname": "Carbo",
        "salary": "$120000",
        "department": "Database Developer"
    }
'

set @var = json_modify(@var, '$.department', 'Game Developer');
select modifiedJson = @var;

Once we change the data, the function should return the new JSON as shown:

SQL Server Convert JSON to Rowset

We can convert a valid JSON data into a SQL Server rowset using the openjson function, a normal SQL Select statement.

The example below uses the OPENJSON() function to transform the JSON into a rowset. We then use the SELECT statement to query the data.

declare @var nvarchar(max)
set @var = '
    {
        "id": 1,
        "firstname": "Rosanne",
        "lastname": "Carbo",
        "salary": "$120000",
        "department": "Database Developer"
    }
'

select *
from openjson(@var)
    with (
        id int 'strict $.id',
        firstname varchar(50) '$.firstname',
        lastname varchar(50) '$.lastname',
        salary money '$.salary',
        department varchar(50) '$.department'
);

The query above should return valid SQL Table as shown:

Although we used a simple JSON string, the OPENJSON() function can handle deeply nested JSON objects.

The strict property in a path requires that a value must exist in the specified property of the JSON string.

Convert SQL Server Data to JSON

You can export SQL Server data into valid JSON using the FOR JSON PATH parameter. For example:

NOTE: In this example, we use the salesdb sample database. You can download the sample database in the resource below:

https://github.com/captainsalem/linuxhint

use salesdb;
select top 5 nameas "product.name", price as "product.price"
from Products
forjsonpath;

The above query the name and price column from the products table, fetch the top 5 records and export them as JSON. The query should return a hyperlink contain the JSON string:

You can open the hyperlink to view the JSON string stored as:

Conclusion

This article helps you to get started on working with JSON data in SQL Server. You are able to validate JSON in SQL Server, modify JSON strings, convert JSON data to a rowset, and how to export SQL Server data to JSON.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list