Wprowadzenie do funkcji JSON w usługach Azure SQL Database i Azure SQL Managed Instance

  • Artykuł

Dotyczy: Azure SQL DatabaseAzure SQL Managed Instance

Usługi Azure SQL Database i Azure SQL Managed Instance umożliwiają analizowanie i wykonywanie zapytań dotyczących danych reprezentowanych w formacie JavaScript Object Notation (JSON) i eksportowanie danych relacyjnych jako tekstu JSON. Dostępne są następujące scenariusze JSON:

Formatowanie danych relacyjnych w formacie JSON

Jeśli masz usługę internetową, która pobiera dane z warstwy bazy danych i udostępnia odpowiedź w formacie JSON, lub struktury języka JavaScript po stronie klienta lub biblioteki, które akceptują dane sformatowane jako JSON, możesz sformatować zawartość bazy danych w formacie JSON bezpośrednio w zapytaniu SQL. Nie musisz już pisać kodu aplikacji, który formatuje wyniki z usługi Azure SQL Database lub azure SQL Managed Instance jako JSON, lub dołączyć bibliotekę serializacji JSON do konwertowania wyników zapytań tabelarycznych, a następnie serializować obiekty w formacie JSON. Zamiast tego możesz użyć klauzuli FOR JSON, aby sformatować wyniki zapytania SQL w formacie JSON i użyć jej bezpośrednio w aplikacji.

W poniższym przykładzie wiersze z Sales.Customer tabeli są formatowane jako dane JSON przy użyciu klauzuli FOR JSON:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

Klauzula PATH for JSON formatuje wyniki zapytania jako tekst JSON. Nazwy kolumn są używane jako klucze, podczas gdy wartości komórek są generowane jako wartości 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"}
]

Zestaw wyników jest sformatowany jako tablica JSON, w której każdy wiersz jest sformatowany jako oddzielny obiekt JSON.

PATH wskazuje, że można dostosować format wyjściowy wyniku JSON przy użyciu notacji kropkowej w aliasach kolumn. Następujące zapytanie zmienia nazwę klucza "CustomerName" w formacie wyjściowym JSON i umieszcza numery telefonów i faksów w obiekcie podrzędnym "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

Dane wyjściowe tego zapytania wyglądają następująco:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

W tym przykładzie zwróciliśmy pojedynczy obiekt JSON zamiast tablicy, określając opcję WITHOUT_ARRAY_WRAPPER . Możesz użyć tej opcji, jeśli wiesz, że zwracasz pojedynczy obiekt w wyniku zapytania.

Główną wartością klauzuli JSON for jest to, że umożliwia zwracanie złożonych danych hierarchicznych z bazy danych sformatowanych jako zagnieżdżone obiekty JSON lub tablice. W poniższym przykładzie pokazano, jak uwzględnić wiersze z Orders tabeli, które należą do Customer obiektu jako zagnieżdżona tablica :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

Zamiast wysyłać oddzielne zapytania w celu pobrania danych klienta, a następnie pobrać listę powiązanych zamówień, możesz pobrać wszystkie niezbędne dane z pojedynczym zapytaniem, jak pokazano w następujących przykładowych danych wyjściowych:

{
  "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"}
  ]
}

Praca z danymi w formacie JSON

Jeśli nie masz ściśle ustrukturyzowanych danych, jeśli masz złożone obiekty podrzędne, tablice lub dane hierarchiczne albo jeśli struktury danych ewoluują wraz z upływem czasu, format JSON może pomóc w reprezentowaniu dowolnej złożonej struktury danych.

JSON to format tekstowy, który może być używany jak każdy inny typ ciągu w usłudze Azure SQL Database i usłudze Azure SQL Managed Instance. Dane JSON można wysyłać lub przechowywać jako standardowe 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

Dane JSON używane w tym przykładzie są reprezentowane przy użyciu typu NVARCHAR(MAX). Kod JSON można wstawić do tej tabeli lub podać jako argument procedury składowanej przy użyciu standardowej składni języka Transact-SQL, jak pokazano w poniższym przykładzie:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Każdy język lub biblioteka po stronie klienta, która współpracuje z danymi ciągów w usługach Azure SQL Database i Azure SQL Managed Instance, będzie również współdziałać z danymi JSON. Kod JSON można przechowywać w dowolnej tabeli obsługującej typ NVARCHAR, taki jak tabela zoptymalizowana pod kątem pamięci lub tabela z wersją systemową. Kod JSON nie wprowadza żadnego ograniczenia w kodzie po stronie klienta lub w warstwie bazy danych.

Wykonywanie zapytań dotyczących danych JSON

Jeśli dane są sformatowane jako dane JSON przechowywane w tabelach usługi Azure SQL, funkcje JSON umożliwiają korzystanie z tych danych w dowolnym zapytaniu SQL.

Funkcje JSON dostępne w usługach Azure SQL Database i Azure SQL Managed Instance umożliwiają traktowanie danych sformatowanych w formacie JSON jako dowolnego innego typu danych SQL. Możesz łatwo wyodrębnić wartości z tekstu JSON i użyć danych JSON w dowolnym zapytaniu:

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

Funkcja JSON_VALUE wyodrębnia wartość z tekstu JSON przechowywanego w kolumnie Dane. Ta funkcja używa ścieżki podobnej do języka JavaScript, aby odwołać się do wartości w tekście JSON w celu wyodrębnienia. Wyodrębniona wartość może być używana w dowolnej części zapytania SQL.

Funkcja JSON_QUERY jest podobna do JSON_VALUE. W przeciwieństwie do JSON_VALUE ta funkcja wyodrębnia złożony obiekt podrzędny, taki jak tablice lub obiekty umieszczone w tekście JSON.

Funkcja JSON_MODIFY umożliwia określenie ścieżki wartości w tekście JSON, który ma zostać zaktualizowany, a także nową wartość, która zastąpi stary. Dzięki temu można łatwo zaktualizować tekst JSON bez ponownej analizy całej struktury.

Ponieważ kod JSON jest przechowywany w standardowym tekście, nie ma gwarancji, że wartości przechowywane w kolumnach tekstowych są prawidłowo sformatowane. Możesz sprawdzić, czy tekst przechowywany w kolumnie JSON jest poprawnie sformatowany przy użyciu standardowych ograniczeń sprawdzania usługi Azure SQL Database i funkcji ISJSON:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Jeśli tekst wejściowy jest poprawnie sformatowany w formacie JSON, funkcja ISJSON zwraca wartość 1. W każdym wstawieniu lub aktualizacji kolumny JSON to ograniczenie sprawdzi, czy nowa wartość tekstowa nie jest nieprawidłowo sformułowana w formacie JSON.

Przekształcanie kodu JSON w format tabelaryczny

Usługi Azure SQL Database i Azure SQL Managed Instance umożliwiają również przekształcanie kolekcji JSON w format tabelaryczny i ładowanie lub wykonywanie zapytań względem danych JSON.

OPENJSON to funkcja wartości tabeli, która analizuje tekst JSON, lokalizuje tablicę obiektów JSON, iteruje przez elementy tablicy i zwraca jeden wiersz w wyniku wyjściowym dla każdego elementu tablicy.

JSON tabular

W powyższym przykładzie możemy określić, gdzie zlokalizować tablicę JSON, która powinna zostać otwarta (w pliku $. Ścieżka orders), jakie kolumny powinny być zwracane w wyniku i gdzie znaleźć wartości JSON, które zostaną zwrócone jako komórki.

Możemy przekształcić tablicę JSON w @orders zmiennej w zestaw wierszy, przeanalizować ten zestaw wyników lub wstawić wiersze do standardowej tabeli:

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

Kolekcja zamówień sformatowanych jako tablica JSON i podana jako parametr procedury składowanej może zostać przeanalizowana i wstawiona do tabeli Orders.