How to use SQL Server JSON functions?

Introduction to the SQL Server JSON value, query and modify functions.

In the world of data management and IT, including of course SQL Server, the use of JSON (JavaScript Object Notation) has become increasingly popular due to its lightweight data-interchange format. As a result, SQL Server has incorporated a set of SQL Server JSON functions in T-SQL to handle JSON data. This tutorial will guide you on how to use these functions, starting from the basics and gradually moving to more complex examples.

1. Understanding JSON Data format in SQL Server

Before diving into the MS SQL JSON functions, it’s crucial to understand what JSON data is. JSON data is a text format that is completely language independent but uses conventions that are familiar to programmers of the C family of languages. In SQL, JSON is stored as NVARCHAR type. Here’s how to create a table with JSON data:

Use [Expert-Only];
 
CREATE TABLE Employees (
    ID INT IDENTITY(1,1),
    Info NVARCHAR(4000)
);

INSERT INTO Employees (Info)
VALUES
   ('{"Name": "John", "Age": 25, "Department": "Sales"}'),
   ('{"Name": "Jane", "Age": 30, "Department": "Marketing"}');

2. The JSON_VALUE function in SQL Server

About Transact-SQL JSON functions, one of the primary functions that stands out is JSON_VALUE. Designed with a simple purpose, the JSON_VALUE function serves the specific role of extracting scalar values from a comprehensive JSON string. Scalar values, in this context, refer to single, non-repeated data points within the JSON structure, such as integers, booleans, or strings

The first function to explore in the Transact-SQL JSON functions is JSON_VALUE. This function extracts a scalar value from a JSON string. Here’s a simple example with a query that will simply return the names of the employees from the JSON data.

 SELECT JSON_VALUE(Info, '$.Name') AS EmployeeName
 FROM Employees
 

The following screenshot group in one image the execution of the two previous T-SQL statement, including these 4 operations:

1. Select the target database
2. Create the Employee table with a JSON column
3. Insert 2 lines using the JSON data format
4. Select the name value using the JSON_VALUE built-in function

3. Working with JSON_QUERY in MS SQL

JSON_QUERY is another function in the suite of SQL Server JSON functions. Unlike JSON_VALUE, JSON_QUERY extracts an object or an array from a JSON string. Here’s below a basic example on how to use it. This query will return the entire JSON data for each employee.

 SELECT JSON_QUERY(Info, '$') AS EmployeeInfo
 FROM Employees
 

4. Exploring the ISJSON Function in T-SQL

In the suite of T-SQL’s JSON functions, ISJSON stands as a practical tool. Its primary role is to ascertain the validity of a string as proper JSON format. When presented with a string, ISJSON will return a value of 1 if the string adheres to the JSON standard, and a 0 if it doesn’t.

To better understand its application, consider the following scenario: A database contains a table named Employees, and within this table, there’s a column named Info. Each entry in the Info column is expected to be in JSON format. If there’s a need to validate this data, the ISJSON function can be employed.

 SELECT ISJSON(Info) AS IsJson
 FROM Employees
 

A result of 1 would indicate that the corresponding ‘Info’ field contains a valid JSON string. If the database is well-maintained and consistent, we can anticipate seeing a 1 for each record, signifying that all ‘Info’ entries are valid JSON.

5. Using the JSON_MODIFY Function in SQL Server

The JSON_MODIFY function in SQL Server JSON functions allows modifying a value in a JSON string. This query will update John’s age to 26 in the JSON data.

 UPDATE Employees
 SET Info = JSON_MODIFY(Info, '$.Age', 26)
 WHERE JSON_VALUE(Info, '$.Name') = 'John'
 

Conclusion about SQL Server JSON Functions

In conclusion, SQL Server provides a robust set of JSON functions to work with JSON data. These functions provide a seamless way to integrate JSON data handling into your SQL operations. Whether you’re extracting values, modifying data, or validating JSON strings, these functions make it easier and more efficient. By mastering these SQL Server JSON functions, you’ll be able to handle JSON data with ease and efficiency in your SQL Server database. Of course SQL Server, like Python, also allows users to manage XML data types using native T-SQL functions.

How to use the XML query() Method in SQL Server?

For further reading, you may want to check out the official Microsoft documentation on JSON functions. Another great resource is this W3Schools tutorial on SQL and JSON.