SQL-сервер JSON

В этом руководстве вы узнаете о SQL Server JSON и о том, как хранить данные JSON, а также извлекать значения JSON.

Что такое JSON

JSON расшифровывается как JavaScript Object Notation.

JSON — это облегченный формат обмена данными, который легко читается человеком и прост для анализа компьютерами.

JSON часто используется для обмена данными между серверами и веб-приложениями. Он также широко используется для файлов конфигурации.

JSON построен на основе двух основных структур данных: объекта и массива.

Объекты

Объект представляет собой неупорядоченную коллекцию пар ключ-значение, заключенных в фигурные скобки {}. Каждая пара состоит из ключа, заключенного в двойные кавычки(«»), двоеточия : и значения.

Например, ниже показан объект JSON, представляющий информацию о человеке:

{"name": "John", "age": 22 }

Объект person имеет два ключа: имя и возраст с соответствующими значениями «Джон» и 22.

Массивы

Массив — это упорядоченный список значений, заключенный в квадратные скобки []. Элементы массива могут иметь разные типы, включая массивы и объекты.

Например, ниже представлен массив JSON, состоящий из числа, строки и объекта JSON:

[1, "person", {"name": "John", "age": 22}]

Типы данных JSON

JSON предлагает несколько типов данных, в том числе:

• Строка: последовательность символов, заключенная в двойные кавычки(«»).
• Число: целое число или число с плавающей точкой.
• Булевое значение: истина или ложь.
• Null: содержит одно нулевое значение.
• Массив: упорядоченный список значений, заключенный в квадратные скобки([]).
• Объект: неупорядоченный список пар ключ-значение, заключенный в фигурные скобки({}).

Проверка JSON

Чтобы проверить, является ли строка допустимым форматом JSON, используйте функцию ISJSON() :

ISJSON(json_string)

Функция ISJSON() возвращает 1, если json_string является допустимым JSON, или 0 в противном случае.

Например, следующий оператор использует функцию ISJSON() для проверки строки JSON:

SELECT ISJSON('{"name": "Joe"}') is_valid_json;

Выход:

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

Он возвращает 1, поскольку строка JSON действительна. Однако следующий оператор возвращает 0, поскольку строка JSON недействительна:

SELECT ISJSON('[1,2,3,]') is_valid_json;

Выход:

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

В строке JSON после цифры 3 стоит запятая, что недопустимо.

Хранение данных JSON в базе данных

SQL Server не предлагает встроенного типа JSON, как PostgreSQL и другие системы баз данных. Вместо этого он использует тип NVARCHAR для хранения данных JSON. Давайте рассмотрим пример хранения данных JSON в таблице.

Сначала создайте таблицу под названием employees для хранения данных о сотрудниках:

CREATE TABLE employees(
   id INT IDENTITY PRIMARY KEY,
   info NVARCHAR(MAX) NOT NULL
);

Таблица сотрудников состоит из двух столбцов:

• id: Это столбец первичного ключа.
• info: Это столбец NVARCHAR, в котором будут храниться данные JSON.

Во-вторых, вставьте две строки в таблицу сотрудников:

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"}}');

Извлечение данных JSON из SQL Server

Чтобы извлечь скалярное значение из строки JSON, используйте функцию JSON_VALUE() :

JSON_VALUE(json_string, json_path)

Функция JSON_VALUE() имеет два параметра:

• json_string — это данные JSON.
• json_path — это путь JSON, который указывает значение из строки JSON для извлечения. Путь JSON — это способ найти элемент в документе JSON.

Например, следующий оператор извлекает имя, возраст и дату рождения из данных JSON, хранящихся в столбце информации таблицы сотрудников:

SELECT 
    JSON_VALUE(info, '$.name') AS Name,
    JSON_VALUE(info, '$.age') AS Age,
    JSON_VALUE(info, '$.dateOfBirth') AS DateOfBirth
FROM 
    employees;

Выход:

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

Чтобы извлечь объект JSON или массив из документа JSON, можно использовать функцию JSON_QUERY() :

JSON_QUERY(json_string, json_path);

Например, следующий оператор использует функцию JSON_QUERY() для извлечения навыков из столбца информации таблицы сотрудников:

SELECT 
  JSON_QUERY(info, '$.skills') skills 
FROM 
  employees;

Выход:

skills
-------------------------------
["JavaScript", "SQL", "Python"]
["Java", "C#", "HTML/CSS"]

В этом примере мы используем функцию JSON_QUERY() для извлечения массива навыков из документов JSON, хранящихся в столбце info.

SQL-сервер предлагает больше полезных функций JSON, позволяющих работать с данными JSON более эффективно.

Краткое содержание

• Используйте тип данных NVARCHAR(MAX) для представления данных JSON в SQL Server.
• Используйте функцию ISJSON(), чтобы проверить, является ли строка допустимым форматом JSON.
• Используйте функцию JSON_VALUE() для извлечения скалярных значений из строки JSON.
• Используйте функцию JSON_QUERY() для извлечения объектов JSON или массивов из строки JSON.