Začínáme s funkcemi JSON ve službě Azure SQL Database a Azure SQL Managed Instance

  • Článek

Platí pro: Azure SQL Database Azure SQL Managed Instance

Azure SQL Database a Azure SQL Managed Instance umožňují analyzovat a dotazovat data reprezentovaná ve formátu JSON (JavaScript Object Notation ) a exportovat relační data jako text JSON. K dispozici jsou následující scénáře JSON:

Formátování relačních dat ve formátu JSON

Pokud máte webovou službu, která přebírá data z databázové vrstvy a poskytuje odpověď ve formátu JSON nebo javascriptové architektury nebo knihovny na straně klienta, které přijímají data formátovaná jako JSON, můžete obsah databáze naformátovat přímo v dotazu SQL. Už nemusíte psát kód aplikace, který formátuje výsledky z Azure SQL Database nebo Azure SQL Managed Instance jako JSON, nebo zahrnout nějakou knihovnu serializace JSON pro převod výsledků tabulkových dotazů a následnou serializaci objektů do formátu JSON. Místo toho můžete pomocí klauzule FOR JSON naformátovat výsledky dotazu SQL jako JSON a použít ho přímo ve vaší aplikaci.

V následujícím příkladu Sales.Customer jsou řádky z tabulky formátované jako JSON pomocí klauzule FOR JSON:

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

Klauzule FOR JSON PATH formátuje výsledky dotazu jako text JSON. Názvy sloupců se používají jako klíče, zatímco hodnoty buněk se generují jako hodnoty 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"}
]

Sada výsledků je naformátovaná jako pole JSON, kde je každý řádek formátovaný jako samostatný objekt JSON.

PATH označuje, že můžete přizpůsobit výstupní formát výsledku JSON pomocí zápisu tečky v aliasech sloupců. Následující dotaz změní název klíče CustomerName ve výstupním formátu JSON a vloží telefonní a faxová čísla do dílčího objektu Kontakt:

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Výstup tohoto dotazu vypadá takto:

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

V tomto příkladu jsme místo pole vrátili jeden objekt JSON zadáním možnosti WITHOUT_ARRAY_WRAPPER . Tuto možnost můžete použít, pokud víte, že v důsledku dotazu vracíte jeden objekt.

Hlavní hodnotou klauzule FOR JSON je, že umožňuje vracet složitá hierarchická data z databáze formátovaná jako vnořené objekty nebo pole JSON. Následující příklad ukazuje, jak zahrnout řádky z Orders tabulky, které patří do Customer vnořené pole 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

Místo odesílání samostatných dotazů k získání dat zákazníků a následnému načtení seznamu souvisejících objednávek můžete získat všechna potřebná data pomocí jednoho dotazu, jak je znázorněno v následujícím ukázkovém výstupu:

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

Práce s daty JSON

Pokud nemáte přísně strukturovaná data, pokud máte složité dílčí objekty, pole nebo hierarchická data nebo pokud se datové struktury v průběhu času vyvíjejí, může vám formát JSON pomoct představovat jakoukoli složitou datovou strukturu.

JSON je textový formát, který se dá použít jako jakýkoli jiný typ řetězce ve službě Azure SQL Database a azure SQL Managed Instance. Data JSON můžete odesílat nebo ukládat jako standardní 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

Data JSON použitá v tomto příkladu jsou reprezentována pomocí typu NVARCHAR(MAX). JSON lze vložit do této tabulky nebo zadat jako argument uložené procedury pomocí standardní syntaxe Jazyka Transact-SQL, jak je znázorněno v následujícím příkladu:

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

Jakýkoli jazyk nebo knihovna na straně klienta, které pracují s řetězcovými daty ve službě Azure SQL Database a azure SQL Managed Instance, budou také pracovat s daty JSON. JSON lze uložit do libovolné tabulky, která podporuje typ NVARCHAR, například tabulku optimalizovanou pro paměť nebo tabulku se systémem. JSON neuvádí žádné omezení v kódu na straně klienta ani v databázové vrstvě.

Dotazování dat JSON

Pokud máte data naformátovaná jako JSON uložená v tabulkách Azure SQL, funkce JSON umožňují tato data použít v jakémkoli dotazu SQL.

Funkce JSON, které jsou k dispozici ve službě Azure SQL Database a Azure SQL Managed Instance, umožňují zpracovávat data formátovaná jako JSON jako jakýkoli jiný datový typ SQL. Hodnoty z textu JSON můžete snadno extrahovat a v libovolném dotazu používat data 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

Funkce JSON_VALUE extrahuje hodnotu z textu JSON uloženého ve sloupci Data. Tato funkce používá javascriptovou cestu k odkazu na hodnotu v textu JSON k extrakci. Extrahovaná hodnota se dá použít v libovolné části dotazu SQL.

Funkce JSON_QUERY se podobá JSON_VALUE. Na rozdíl od JSON_VALUE tato funkce extrahuje komplexní dílčí objekt, jako jsou pole nebo objekty umístěné v textu JSON.

Funkce JSON_MODIFY umožňuje zadat cestu k hodnotě v textu JSON, který se má aktualizovat, a také novou hodnotu, která přepíše starou hodnotu. Tímto způsobem můžete snadno aktualizovat text JSON, aniž byste museli oddělit celou strukturu.

Vzhledem k tomu, že json je uložený ve standardním textu, nejsou zaručené správné formátování hodnot uložených v textových sloupcích. Pomocí standardních omezení kontroly služby Azure SQL Database a funkce ISJSON můžete ověřit, že text uložený ve sloupci JSON je správně naformátovaný:

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

Pokud je vstupní text správně naformátovaný ve formátu JSON, vrátí funkce ISJSON hodnotu 1. Při každém vložení nebo aktualizaci sloupce JSON toto omezení ověří, že nová textová hodnota není chybně formátovaná ve formátu JSON.

Transformace JSON do tabulkového formátu

Azure SQL Database a Azure SQL Managed Instance také umožňují transformovat kolekce JSON do tabulkového formátu a načítat nebo dotazovat data JSON.

OPENJSON je funkce table-value, která parsuje text JSON, vyhledá pole objektů JSON, iteruje prvky pole pole a vrátí jeden řádek ve výstupním výsledku pro každý prvek pole.

Tabulkové soubory JSON

V předchozím příkladu můžeme určit, kam se má najít pole JSON, které by se mělo otevřít (v $. Cesta Objednávky), které sloupce by se měly vrátit jako výsledek, a kde najít hodnoty JSON, které se vrátí jako buňky.

Pole JSON v @orders proměnné můžeme transformovat na sadu řádků, analyzovat tuto sadu výsledků nebo vložit řádky do standardní tabulky:

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

Kolekce objednávek formátovaných jako pole JSON a poskytovaná jako parametr uložené procedury se dá analyzovat a vkládat do tabulky Orders.