SQL-Ex blog

Руководство включает следующие пункты:

• Синтаксис JSON


• Пример JSON_OBJECT с атрибутами-строками


• Пример JSON_OBJECT с атрибутами-числами


• Пример JSON_OBJECT с атрибутами NULL


• Вариант JSON_OBJECT с опцией Absent on Null


• JSON_OBJECT с несколькими атрибутами


• Пример JSON_OBJECT с числами


• Пример JSON_OBJECT со строками


• JSON_OBJECT с массивом объектов


• Объекты с объектными атрибутами


• Как получить информацию из таблиц и функций с помощью JSON_OBJECT

Требования

1. Установленный SQL Server 2022


2. Установленная SSMS


3. Для последнего примера я буду использовать базу данных Adventureworks

Введение в JSON

JSON означает JavaScript Object Notation (нотация объектов JavaScript). Это формат, используемый в тех же целях, что файл XML. Он применяется для обмена данными, используя REST API, веб-службы и другие приложения.

Синтаксис JSON

Простой атрибут в JSON выглядит так:

{"name":"Robert"}

Обратите внимание, что используются фигурные скобки. Имя атрибута указывается в двойных кавычках, за которым следует двоеточие, а затем значение атрибута, которое, если это строка, тоже использует двойные кавычки.

Если значением является число, оно не требует двойных кавычек, как показано ниже.

{"age":57}

Для обработки NULL-значений используется null:

{"Name":null}

Следующий пример показывает объект с именем actors, который хранит атрибуты актера (name, last name, age):

{"name":"Robert","lastname":"Downey", "age":57}

Замечание: атрибуты разделяются запятыми.

В JSON вы можете также использовать массивы. Массивы используют квадратные скобки. Следующий пример показывает, как работать с массивами. Для чисел вам не нужно заключать значения в двойные кавычки:

[1,2,4]

Для массива строк требуются двойные кавычки:

["John","Bran","Sansa"]

Для нескольких записей мы можем использовать нечто подобное:

[
{"name":"Robert","lastname":"Downey","age":57},
{"name":"Chris","lastname":"Hemsworth","age":39}
]

Мы использовали квадратные скобки и каждый элемент (в этом примере - актер) разделен запятыми.

Для иерархических данных у вас может быть что-то вроде этого. В данном примере я создам небольшую иерархическую структуру. Мать (Rhaenrya Targaryen из House of the Dragon) имеет двух детей: Jacaerys и Lucerys.



Синтаксис JSON будет подобен следующему:

{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
{
"name":"Jacaerys",
"lastname":"Velaryon",
"age":20
},
{
"name":"Lucerys",
"lastname":"Velaryon",
"age":18
}
]
}
Теперь мы поучимся, как строить эти структуры JSON в T-SQL, используя JSON_OBJECT и JSON_ARRAY.

Пример JSON_OBJECT со строковым атрибутом

Следующий пример создает атрибут name со значением Robert в формате JSON.

SELECT JSON_OBJECT('name':'Robert') attribute



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

Пример JSON_OBJECT с числовым атрибутом

Этот пример использует числовые атрибуты.

SELECT JSON_OBJECT('age':57) attribute



Заметим, что число не требуется заключать в кавычки.

Пример JSON_OBJECT с атрибутом, имеющим значение NULL

Мы можем использовать значения NULL.

SELECT JSON_OBJECT('name':null) attribute



Значения NULL не требуют кавычек.

JSON_OBJECT с опцией Absent on Null

Опция Absent on Null не будет показывать атрибут, если его значение есть NULL.

В этом примере, если значение не NULL, то оно будет отображаться:

SELECT JSON_OBJECT('name':2 ABSENT ON NULL) attribute



Но если значение NULL имеется в JSON, то оно не будет отображаться:

SELECT JSON_OBJECT('name':null ABSENT ON NULL) attribute

JSON_OBJECT с несколькими атрибутами

Если используется несколько атрибутов, они разделяются запятыми.

SELECT JSON_OBJECT('name':'Robert','lastname':'Downey','age':57) attributes

Пример JSON_ARRAY с числами

В этом примере мы покажем, как создать массив JSON, используя функцию JSON_ARRAY.

SELECT JSON_ARRAY(1,2,4) myArray

Пример JSON_ARRAY со строками

Функция JSON_ARRAY использует одинарные кавычки для строк.

SELECT JSON_ARRAY('John','Bran','Sansa') myArray

JSON_ARRAY с массивом объектов

Следующий пример показывает, как отобразить несколько актеров. Для этого примера мы создадим массив из двух элементов. Каждый элемент имеет свои атрибуты. В этом примере элементами служат актеры Robert и Chris.

SELECT JSON_ARRAY
(JSON_OBJECT('name':'Robert','lastname':'Downey','age':57),
(JSON_OBJECT('name':'Chris','lastname':'Hemsworth','age':39))) array



Если вы хотите отформатировать данные, чтобы их было легче читать, вы можете использовать парсер JSON. Имеется много бесплатных онлайн-инструментов, с их помощью представление данных легче читается.

Формат без парсера

[{"name":"Robert","lastname":"Downey","age":57},{"name":"Chris","lastname":"Hemsworth","age":39}]

Формат c парсером

[
{
"name":"Robert",
"lastname":"Downey",
"age":57
},
{
"name":"Chris",
"lastname":"Hemsworth",
"age":39
}
]

Объекты с объектными атрибутами

В некоторых сценариях у вас имеются иерархические атрибуты. Этот пример показывает пример родительско-дочерней иерархии, упомянутый ранее для Rhaenrya и ее детей.

SELECT JSON_OBJECT(
'Name':'Rhaenyra',
'Lastname':'Targaryen',
'Children':
JSON_ARRAY
(JSON_OBJECT('name':'Jacaerys','lastname':'Velaryon', 'age':20),
(JSON_OBJECT('name':'Lucerys','lastname':'Velaryon', 'age':18)))
) json

На выходе получим:

{
"Name":"Rhaenyra",
"Lastname":"Targaryen",
"Children":[
{
"name":"Jacaerys",
"lastname":"Velaryon",
"age":20
},
{
"name":"Lucerys",
"lastname":"Velaryon",
"age":18
}
]
}

Получение информации из таблиц и функций с помощью JSON_OBJECT

Наконец, представим пример, использующий данные из таблицы Person.person в базе данных Adventureworks и функцию GETDATE.

SELECT JSON_OBJECT('Firstname':FirstName,'Lastname':LastName,'Time':getdate()) attribute
FROM [Person].[Person]

Ссылки по теме
1. JSON для SQL Server. Часть 1

2. JSON в SQL Server - часть 2

3. Примеры функций SQL Server для работы с файлами JSON

4. Изменения языка T-SQL в SQL Server 2022: часть 3