使用 FOR JSON 將查詢結果格式化為 JSON (SQL Server)

適用于:SQL Server 2016 (13.x) 和更新版本

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

注意

Azure Data Studio 是 JSON 查詢的建議使用查詢編輯器,因為此編輯器會自動格式化 JSON 結果 (如本文所示),而非顯示一般字串。

當您使用 FOR JSON 子句時,可以明確指定 JSON 輸出的結構,或讓 SELECT 語句的結構決定輸出。

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

  • 若要根據 SELECT 語句的結構自動格式化 JSON 輸出,請使用 FOR JSON AUTO

以下是 SELECT 語句的範例, FOR JSON 其中包含 子句及其輸出。

顯示 FOR JSON 運作方式的圖表。

選項 1 - 使用 FOR JSON PATH 控制輸出

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

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

FOR JSON 輸出流程圖。

FOR JSON PATH 的詳細資訊

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

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

選項 2 - 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"
}]

2.b - 使用 JOIN 和 NULL 的範例

下列範例 SELECT...FOR JSON AUTO 包含當聯結資料表的資料之間有 1:多 關聯性時,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 輸出。

FOR JSON 子句的輸出

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

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

    • 小型結果集會包含單一資料列。
    • 大型結果集跨多個資料列分割太長的 JSON 字串。
      • 根據預設,SQL Server Management Studio (SSMS) 會在輸出設定為 [以方格顯示結果] 時,將結果串連成單一資料列。 SSMS 的狀態列會顯示實際資料列計數。

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

        SQL Server Management Studio中 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"
}]

另請參閱

下一步