SQL Server JSON

Summary: in this tutorial, you will learn about SQL Server JSON and how to store JSON data, as well as retrieve JSON values.

What is JSON

JSON stands for JavaScript Object Notation.

JSON is a lightweight data-interchange format that is easy for humans to read and simple for computers to parse.

JSON is often used to exchange data between servers and web applications. It is also commonly used for configuration files.

JSON is built based on two main data structures: object and array.

Objects

An object is an unordered collection of key-value pairs enclosed in curly braces {}. Each pair consists of a key surrounded by double quotes (""), a colon :, and a value.

For example, the following shows a JSON object that represents the information of a person:

{"name": "John", "age": 22 }Code language: SQL (Structured Query Language) (sql)

The person object has two keys name and age with the corresponding values "John" and 22.

Arrays

An array is an ordered list of values enclosed in square brackets []. The items in the array may have different types, including arrays and objects.

For example, the following is a JSON array of a number, a string, and a JSON object:

[1, "person", {"name": "John", "age": 22}]Code language: SQL (Structured Query Language) (sql)

JSON data types

JSON offers some data types, including:

• String: a sequence of characters enclosed in double quotes ("").
• Number: an integer or floating-point number.
• Boolean: true or false.
• Null: contains one null value.
• Array: an ordered list of values enclosed in square brackets ([]).
• Object: An unordered list of key-value pairs enclosed in curly braces ({}).

Validating JSON

To check if a string is valid JSON or not, you use the ISJSON() function:

ISJSON(json_string)Code language: SQL (Structured Query Language) (sql)

The ISJSON() function returns 1 if the json_string is a valid JSON or 0 otherwise.

For example, the following statement uses the ISJSON() function to validate a JSON string:

SELECT ISJSON('{"name": "Joe"}') is_valid_json;Code language: SQL (Structured Query Language) (sql)

Output:

is_valid_json
-------------
1

It returns 1 because the JSON string is valid. However, the following statement returns 0 because the JSON string is not valid:

SELECT ISJSON('[1,2,3,]') is_valid_json;Code language: SQL (Structured Query Language) (sql)

Output:

is_valid_json
-------------
0

The JSON string has a comma after the number 3, which is not valid.

Storing JSON data in the database

SQL Server does not offer a built-in JSON type like PostgreSQL and other database systems. Instead, it uses the NVARCHAR type to store JSON data. Let’s take an example of storing JSON data in a table.

First, create a table called employees to store the employee data:

CREATE TABLE employees(
   id INT IDENTITY PRIMARY KEY,
   info NVARCHAR(MAX) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The employees table has two columns:

• id: This is the primary key column.
• info : This is an NVARCHAR column that will store JSON data.

Second, insert two rows into the employees table:

INSERT INTO employees (info)
VALUES 
    ('{"name": "John", "age": 35, "dateOfBirth": "1989-05-15", "skills": ["JavaScript", "SQL", "Python"], "address": {"street": "123 Main St", "city": "New York", "state": "NY", "country": "USA"}}'),
    ('{"name": "Alice", "age": 28, "dateOfBirth": "1996-10-22", "skills": ["Java", "C#", "HTML/CSS"], "address": {"street": "456 Elm St", "city": "Los Angeles", "state": "CA", "country": "USA"}}');Code language: SQL (Structured Query Language) (sql)

Extracting JSON data from SQL Server

To extract a scalar value from a JSON string, you use the JSON_VALUE() function:

JSON_VALUE(json_string, json_path)Code language: SQL (Structured Query Language) (sql)

The JSON_VALUE() function has two parameters:

• json_string is JSON data.
• json_path is a JSON path that specifies the value from the JSON string to extract. A JSON path is a way to locate an element in a JSON document.

For example, the following statement extracts the name, age, and dateOfBirth from the JSON data stored in the info column of the employees table:

SELECT 
    JSON_VALUE(info, '$.name') AS Name,
    JSON_VALUE(info, '$.age') AS Age,
    JSON_VALUE(info, '$.dateOfBirth') AS DateOfBirth
FROM 
    employees;Code language: SQL (Structured Query Language) (sql)

Output:

Name  | Age | DateOfBirth
--------------------------
John  | 35  | 1989-05-15
Alice | 28  | 1996-10-22

To extract a JSON object or array from a JSON document, you can use the JSON_QUERY() function:

JSON_QUERY(json_string, json_path);Code language: SQL (Structured Query Language) (sql)

For example, the following statement uses the JSON_QUERY() function to extract the skills from the info column of the employees table:

SELECT 
  JSON_QUERY(info, '$.skills') skills 
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

skills
-------------------------------
["JavaScript", "SQL", "Python"]
["Java", "C#", "HTML/CSS"]Code language: CSS (css)

In this example, we use the JSON_QUERY() function to extract the skills array from JSON documents stored in the info column.

SQL server offers more useful JSON functions, allowing you to work with JSON data more effectively.

Summary

• Use the NVARCHAR(MAX) data type to represent JSON data in SQL Server.
• Use the ISJSON() function to check if a string is a valid JSON or not.
• Use the JSON_VALUE() function to extract scalar values from a JSON string.
• Use the JSON_QUERY() function to extract JSON objects or arrays from a JSON string.