共用方式為

開始使用 Azure SQL 資料庫 和 Azure SQL 受控執行個體 中的 JSON 功能

  • 發行項

適用於：Azure SQL 資料庫 Azure SQL 受控執行個體

Azure SQL 資料庫和Azure SQL 受控執行個體讓您可以剖析及查詢以 JavaScript 物件標記法 (JSON) 格式所示的資料，然後將您的關聯式資料匯出成 JSON 文字。 可用的 JSON 案例如下：

採用 JSON 格式的關聯式資料

如果您有從資料庫層取得資料，並將其以 JSON 格式回傳的 Web 服務，或有接受 JSON 資料格式的用戶端 JavaScript 架構或程式庫，可以在 SQL 查詢中直接將 資料庫內容格式化為 JSON。 您不再需要寫入應用程式的程式碼，將 Azure SQL 資料庫 或 Azure SQL 受控執行個體的結果格式化為 JSON，或包括一些 JSON 序列化程式庫，以將表格式查詢結果轉換為 JSON 格式。 作為替代，您可以使用 FOR JSON 子句，將 SQL 查詢結果格式化為 JSON，並直接用於應用程式。

在下列範例中，Sales.Customer 資料表中的資料列會使用 FOR JSON 子句來格式化為 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 結果的輸出格式。 下列查詢會變更採用輸出 JSON 格式的「CustomerName」索引鍵名稱，並將電話號碼和傳真號碼放在「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"
    }
}

在此範例中，我們藉由指定 [WITHOUT_ARRAY_WRAPPER] 選項回傳單一 JSON 物件，而非陣列。 如果您知道會以查詢結果回傳單一物件，則可使用此選項。

FOR JSON 子句的主要值是指，可讓您從格式化為巢狀 JSON 物件或陣列的資料庫回傳複雜的階層式資料。 下列範例顯示了如何將包括屬於 Customer 的 Orders 資料表中的資料列，做為 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

您可透過單一查詢來取得所有必要資料，而不用傳送單獨的查詢來取得客戶資料，再擷取相關訂單清單，如下列範例輸出所示：

{
  "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 是一種文字格式，可像用於 Azure SQL 資料庫 和 Azure SQL 受控執行個體中的任何其他字串類型。 您可將 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"]}'

可搭配 Azure SQL 資料庫和 Azure SQL 受控執行個體中字串資料使用的任何用戶端語言或程式庫將均可搭配 JSON 資料使用。 JSON 可儲存在任何支援 NVARCHAR 類型的資料表，例如經記憶體最佳化的資料表或系統設定版本的資料表。 JSON 不會在用戶端程式碼或資料庫層中導入任何條件約束。

查詢 JSON 資料

如果您的資料格式化為儲存在 Azure SQL 資料表中的 JSON，JSON 函數可讓您在任何 SQL 查詢中使用此資料。

Azure SQL 資料庫和 Azure 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 會儲存於標準文字，因此不保證儲存在文字資料欄中的值格式正確。 您可以使用標準 Azure SQL 資料庫來檢查條件約束和 ISJSON 函式，確認儲存在 JSON 資料欄中的文字已正確格式化：

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

如果輸入文字已正確格式化為 JSON，則 ISJSON 函式會回傳值 1。 在 JSON 資料欄的每次插入或更新，此條件約束將會驗證新的文字值並未採用格式錯誤的 JSON。

將 JSON 轉換成表格式的格式

Azure SQL 資料庫 和 Azure SQL 受控執行個體還可讓您將 JSON 集錦轉換為表格式的格式，以及載入或查詢 JSON 資料。

OPENJSON 是一個資料表值函式，可剖析 JSON 文字、定位 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 陣列的訂單集錦，並作為參數提供給預存程序，以進行剖析並插入訂單資料表。