Примечание.
Для доступа к этой странице требуется авторизация. Вы можете попробовать войти или изменить каталоги.
Для доступа к этой странице требуется авторизация. Вы можете попробовать изменить каталоги.
Область применения:
База данных SQL Azure Управляемый экземпляр SQL Azureбазе данных SQL в Fabric
База данных SQL Azure, база данных SQL в Microsoft Fabric и Управляемый экземпляр SQL Azure позволяют анализировать и запрашивать данные, представленные в формате нотации объектов JavaScript (JSON), и экспортировать реляционные данные в виде текста JSON. Доступны следующие сценарии использования JSON.
-
Преобразование реляционных данных в формат JSON с помощью предложения
FOR JSON - Работа с данными JSON
- Запрос данных JSON с помощью скалярных функций JSON
-
Преобразование данных JSON в табличный формат с помощью функции
OPENJSON
Форматирование реляционных данных в формате JSON
При наличии веб-службы, которая извлекает данные из слоя базы данных и представляет ответ в формате JSON, либо клиентских платформ или библиотек JavaScript, которые принимают данные в формате JSON, можно преобразовывать содержимое базы данных в формат JSON непосредственно в SQL-запросе. Вам больше не нужно писать код приложения, который форматирует результаты в формате JSON или включать в нее библиотеку сериализации JSON, чтобы преобразовать результаты табличного запроса, а затем сериализовать объекты в формат JSON. Вместо этого можно использовать FOR JSON предложение для форматирования результатов SQL-запроса в формате JSON и их использования непосредственно в приложении.
В следующем примере строки из таблицы Sales.Customer преобразовываются в формат JSON с помощью предложения FOR JSON.
select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH
Предложение FOR JSON PATH форматирует результаты запроса в виде текста JSON. Имена столбцов используются в качестве ключей, а значения ячеек создаются как значения JSON.
[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]
Результирующий набор форматируется как массив JSON, в котором каждая строка форматируется как отдельный объект JSON.
PATH указывает, что можно настроить выходной формат результата JSON с помощью нотации точек в псевдонимах столбцов. Следующий запрос изменяет имя ключа CustomerName в выходном формате JSON и помещает номера телефонов и факсов в под-объект Contact.
select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Выходные данные этого запроса имеют следующий вид.
{
"Name":"Nada Jovanovic",
"Contact":{
"Phone":"(215) 555-0100",
"Fax":"(215) 555-0101"
}
}
В этом примере мы вернули отдельный объект JSON вместо массива, указав параметр WITHOUT_ARRAY_WRAPPER. Этот параметр можно использовать, если вы знаете, что результатом запроса является отдельный объект.
Основное значение FOR JSON предложения заключается в том, что он позволяет возвращать сложные иерархические данные из базы данных, форматируемой как вложенные объекты JSON или массивы. В приведенном ниже примере показано, как добавить строки из таблицы Orders, принадлежащие Customer, в виде вложенного массива объектов Orders.
select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
join Sales.Orders Orders
on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
Вместо отправки отдельных запросов для получения Customer данных и получения списка связанных Ordersданных можно получить все необходимые данные с одним запросом, как показано в следующем примере выходных данных:
{
"Name":"Nada Jovanovic",
"Phone":"(215) 555-0100",
"Fax":"(215) 555-0101",
"Orders":[
{"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
{"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
{"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
]
}
Работа с данными JSON
Если у вас нет строго структурированных данных, имеются сложные вложенные объекты, массивы или иерархические данные либо структуры данных со временем развиваются, то формат JSON позволит представить любую сложную структуру данных.
JSON — это текстовый формат, который можно использовать как любой другой строковый тип. Вы можете отправлять или хранить данные JSON в виде стандартной nvarchar:
CREATE TABLE Products (
Id int identity primary key,
Title nvarchar(200),
Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
insert into Products(Title, Data)
values(@title, @json)
END
Данные JSON, используемые в этом примере, представлены с помощью типа nvarchar(MAX). Данные JSON можно вставить в эту таблицу или указать в качестве аргумента хранимой процедуры, используя стандартный синтаксис Transact-SQL, как показано в следующем примере.
EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'
Любой клиентский язык или библиотека, работающая с строковыми данными, также будет работать с данными JSON. JSON можно хранить в любой таблице, поддерживающей тип nvarchar , например таблицу, оптимизированную для памяти, или таблицу с системной версией. Формат JSON не накладывает каких-либо ограничений на код на стороне клиента или на уровне базы данных.
Запрос данных JSON
Если у вас есть данные в формате JSON, хранящиеся в таблицах, функции JSON позволяют использовать эти данные в любом запросе SQL.
Функции JSON позволяют обрабатывать данные в формате JSON как любой другой тип данных SQL. Можно легко извлечь значения из текста JSON и использовать данные JSON в любом запросе.
select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'
update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1
Функция JSON_VALUE извлекает значение из текста JSON, хранящегося в столбце данных. Эта функция использует путь, как в JavaScript, для указания ссылки на извлекаемое значение в тексте JSON. Извлеченное значение может использоваться в любой части SQL-запроса.
Функция JSON_QUERY похожа на JSON_VALUE. В отличие от JSON_VALUE этой функции, эта функция извлекает сложные подобъекты, такие как массивы или объекты, помещенные в текст JSON.
Функция JSON_MODIFY позволяет указать путь к значению в тексте JSON, который должен быть обновлен, а также новое значение, которое перезаписывает старое. Таким образом можно легко обновить текст JSON, не анализируя всю структуру.
Так как данные JSON хранятся в виде обычного текста, то нет никакой гарантии, что значения, хранящиеся в текстовых столбцах, отформатированы правильно. Вы можете убедиться, что текст, хранящийся в столбце JSON, правильно отформатирован с помощью стандартных ограничений ISJSON проверки и функции:
ALTER TABLE Products
ADD CONSTRAINT [Data should be formatted as JSON]
CHECK (ISJSON(Data) > 0)
Если входной текст правильно отформатирован JSON, ISJSON функция возвращает значение 1. При каждой операции вставки или обновления столбца JSON это ограничение будет проверять, правилен ли формат JSON нового текстового значения.
Преобразование JSON в табличный формат
Коллекции JSON можно преобразовать в табличный формат и загрузить или запросить данные JSON.
OPENJSON — это функция T-SQL табличного значения, которая анализирует текст JSON, находит массив объектов JSON, выполняет итерацию элементов массива и возвращает одну строку в выходных результатах для каждого элемента массива.
В примере можно указать, где найти массив JSON, который должен быть открыт (в $.Orders пути), какие столбцы должны возвращаться в результате, а также где найти значения JSON, которые будут возвращены в виде ячеек.
В переменной @orders можно преобразовать массив JSON в набор строк, проанализировать этот результирующий набор или вставить строки в стандартную таблицу.
CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN
insert into Orders(Number, Date, Customer, Quantity)
select Number, Date, Customer, Quantity
FROM OPENJSON (@orders)
WITH (
Number varchar(200),
Date datetime,
Customer varchar(200),
Quantity int
)
END
Набор заказов, отформатированный как массив JSON и указанный в качестве параметра хранимой процедуры, можно проанализировать и вставить в таблицу Orders.