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

  • 發行項

適用於：Azure SQL DatabaseAzure SQL 受控執行個體

Azure SQL 資料庫 和 Azure SQL 受控執行個體 可讓您剖析和查詢以 JavaScript 物件表示法 （JSON） 格式表示的數據，並將關係型數據匯出為 JSON 文字。 下列 JSON 案例可供使用：

以 JSON 格式格式化關係型數據

如果您的 Web 服務會從資料庫層擷取數據，並以 JSON 格式提供回應，或接受格式化為 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 函式可讓您將格式化的數據視為任何其他 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函式會從儲存在 Data 資料行中的 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 tabular

在上述範例中，我們可以指定在何處找出應該開啟的 JSON 陣列（在 $中。排序路徑）、應該傳回哪些數據行作為結果，以及在何處尋找將當做單元格傳回的 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 資料表。