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

適用範圍:SQL Server 2016 (13.x) 及更新版本

將查詢結果格式化為 json,或將子句加入至語句,以將資料從 SQL Server 匯出為 json FOR JSON SELECT 。 使用 FOR JSON 子句可將應用程式的 JSON 輸出格式設定委派給,以簡化用戶端應用程式 SQL Server 。 Azure Data Studio 是 JSON 查詢的建議使用查詢編輯器,因為此編輯器會自動格式化 JSON 結果 (如本文所示),而非顯示一般字串。

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

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

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

以下是具有 FOR JSON 子句和其輸出的 SELECT 語句範例。

FOR JSON

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

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

以下是使用 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 範例會顯示當已 JOIN 的資料表資料間有 1:Many 關聯性時,JSON 結果的外觀。

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

go

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 OUTER 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 輸出

      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 和 Azure SQL Database 中的 JSON

Microsoft 影片

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介,請參閱下列影片:

下一步