使用 FOR JSON 將查詢結果格式化為 JSON

適用於： SQL Server 2016 (13.x) 以上版本 Azure SQL 資料庫Azure SQL 受控執行個體Azure Synapse Analytics (僅無伺服器 SQL 集區)

將 FOR JSON 子句新增至 SELECT 陳述式，以將查詢結果格式化為 JSON，或將 SQL Server 中的資料匯出為 JSON。 使用 FOR JSON 子句，將來自應用程式的 JSON 輸出格式設定委派給 SQL Server，以簡化用戶端應用程式。

注意

Azure Data Studio 是 JSON 查詢的建議使用查詢編輯器，因為此編輯器會自動格式化 JSON 結果，如本文所示。 SQL Server Management Studio 會顯示未格式化的字串。

格式化查詢結果

使用 FOR JSON 子句時，可以明確指定 JSON 輸出的結構，或讓 SELECT 陳述式的結構決定輸出。

• 若要保有對 JSON 輸出格式的完整控制權，請使用 FOR JSON PATH。 您可以建立包裝函式物件和巢狀複雜屬性。

• 若要根據 SELECT 陳述式的結構自動格式化 JSON 輸出，請使用 FOR JSON AUTO。

以下是含 FOR JSON 子句的 SELECT 陳述式範例和其輸出。

FOR JSON PATH

使用 FOR JSON PATH 控制輸出

在 PATH 模式中，您可以使用點語法 (例如 Item.Price) 來格式化巢狀輸出。

以下是使用 PATH 模式搭配 FOR JSON 子句的範例查詢。 下列範例也會使用 ROOT 選項來指定具名根項目。

FOR JSON PATH 的詳細資訊

如需詳細資訊和範例，請參閱以 PATH 模式格式化巢狀 JSON 輸出 (SQL Server)。

如需語法和使用方式，請參閱 FOR 子句 (Transact-SQL)。

FOR JSON AUTO

SELECT 陳述式使用 FOR JSON AUTO 控制輸出

在 AUTO 模式中，SELECT 陳述式的結構決定 JSON 輸出的格式。

根據預設，NULL 值不會包含在輸出中。 您可以使用 INCLUDE_NULL_VALUES 變更此行為。

以下是使用 AUTO 模式搭配 FOR JSON 子句的範例查詢。

SELECT name, surname
FROM emp
FOR JSON AUTO;

這是傳回的 JSON。

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

JOIN 和 NULL 的範例

下列 SELECT...FOR JSON AUTO 範例會顯示當聯結的資料表資料之間有 1:many 關聯性時，JSON 結果的外觀。

也會示範傳回的 JSON 中沒有 Null 值。 不過，您可以在 FOR 子句中使用 INCLUDE_NULL_VALUES 關鍵字來覆寫此預設行為。

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

接下來是前述 SELECT 輸出的 JSON。

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

FOR JSON AUTO 的詳細資訊

如需詳細資訊和範例，請參閱使用 AUTO 模式自動格式化 JSON 輸出 (SQL Server)。

如需語法和使用方式，請參閱 FOR 子句 (Transact-SQL)。

控制其他 JSON 輸出選項

使用下列其他選項來控制 FOR JSON 子句的輸出。

• ROOT

  若要將單一最上層元素新增至 JSON 輸出，請指定 ROOT 選項。 如果您未指定此選項，JSON 輸出就不會有根項目。 如需詳細資訊，請參閱將根節點與根選項加入至 JSON 輸出 (SQL Server)。

• INCLUDE_NULL_VALUES

  若要在 JSON 輸出中包含 Null 值，請指定 INCLUDE_NULL_VALUES 選項。 如果您未指定此選項，輸出就不會在查詢結果中包含 NULL 值的 JSON 屬性。 如需詳細資訊，請參閱在 JSON - INCLUDE_NULL_VALUES 中包含 Null 值選項。

• WITHOUT_ARRAY_WRAPPER

  若要移除預設圍住 FOR JSON 子句之 JSON 輸出的方括弧，請指定 WITHOUT_ARRAY_WRAPPER 選項。 使用此選項以產生單一 JSON 物件，作為來自單一資料列結果的輸出。 如果未指定此選項，JSON 輸出就會格式化為陣列，也就是說，輸出以方括弧括住。 如需詳細資訊，請參閱從 JSON - WITHOUT_ARRAY_WRAPPER 中移除方括弧選項。

FOR JSON 子句的輸出

FOR JSON 子句的輸出具有下列特性：

1. 結果集包含單一資料行。

   • 小型結果集會包含單一資料列。
   • 大型結果集跨多個資料列分割太長的 JSON 字串。

     • 根據預設，SQL Server Management Studio (SSMS) 會在輸出設定為 [以方格顯示結果] 時，將結果串連成單一資料列。 SSMS 的狀態列會顯示實際資料列計數。

     • 其他用戶端應用程式可能需要程式碼，藉由串連多個資料列的內容，來將較長的結果重新合併成有效的單一 JSON 字串。 如需這個程式碼在 C# 應用程式中的範例，請參閱在 C# 用戶端應用程式中使用 FOR JSON 輸出。

       

2. 結果會格式化為 JSON 物件陣列。

   • JSON 陣列中的元素數目等於 SELECT 陳述式結果中的資料列數目 (在套用 FOR JSON 子句之前)。

   • SELECT 陳述式 (在套用 FOR JSON 子句之前) 結果中的每個資料列會成為陣列中的個別 JSON 物件。

   • SELECT 陳述式 (在套用 FOR JSON 子句之前) 結果中的每個資料行則會成為 JSON 物件的屬性。

3. 資料行名稱與其值會根據 JSON 語法逸出。 如需詳細資訊，請參閱 FOR JSON 如何逸出特殊字元和控制字元 (SQL Server)。

範例

以下是示範 FOR JSON 子句如何格式化 JSON 輸出的範例。

查詢結果

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

JSON 輸出

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

相關內容

• FOR JSON 如何將 SQL Server 資料類型轉換為 JSON 資料類型 (SQL Server)
• FOR JSON 如何逸出特殊字元和控制字元 (SQL Server)
• NoSQL 與關聯式領域之間的橋樑 JSON
• SELECT - FOR 子句 (Transact-SQL)
• 在 SQL Server 和用戶端應用程式中使用 FOR JSON 輸出 (SQL Server)