使用 FOR JSON 將查詢結果格式化為 JSON (SQL Server)Format Query Results as JSON with FOR JSON (SQL Server)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

FOR JSON 子句新增至 SELECT 陳述式,以將查詢結果格式化為 JSON,或將 SQL Server 中的資料匯出為 JSON。Format query results as JSON, or export data from SQL Server as JSON, by adding the FOR JSON clause to a SELECT statement. 使用 FOR JSON 子句,將來自應用程式的 JSON 輸出格式設定委派給 SQL ServerSQL Server,以簡化用戶端應用程式。Use the FOR JSON clause to simplify client applications by delegating the formatting of JSON output from the app to SQL ServerSQL Server.

當您使用 FOR JSON 子句時,您可以明確指定 JSON 輸出的結構,或讓 SELECT 陳述式的結構決定輸出。When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

  • 若要保有對 JSON 輸出格式的完整控制權,請使用 FOR JSON PATHTo maintain full control over the format of the JSON output, use FOR JSON PATH. 您可以建立包裝函式物件和巢狀複雜屬性。You can create wrapper objects and nest complex properties.

  • 若要根據 SELECT 陳述式的結構自動格式化 JSON 輸出,請使用 FOR JSON AUTOTo format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

以下是含 FOR JSON 子句的 SELECT 陳述式範例和其輸出。Here's an example of a SELECT statement with the FOR JSON clause and its output.

FOR JSON

選項 1 - 使用 FOR JSON PATH 控制輸出Option 1 - You control output with FOR JSON PATH

PATH 模式中,您可以使用點語法 (例如 'Item.UnitPrice') 來格式化巢狀輸出。In PATH mode, you can use the dot syntax - for example, 'Item.UnitPrice' - to format nested output.

以下是使用 PATH 模式搭配 FOR JSON 子句的範例。Here's a sample query that uses PATH mode with the FOR JSON clause. 下列範例也會使用 ROOT 選項來指定具名根項目。The following example also uses the ROOT option to specify a named root element.

FOR JSON 輸出的流程圖表

FOR JSON PATH 的詳細資訊More info about FOR JSON PATH

如需詳細資訊和範例,請參閱以 PATH 模式格式化巢狀 JSON 輸出 (SQL Server)For more detailed info and examples, see Format Nested JSON Output with PATH Mode (SQL Server).

如需語法和使用方式,請參閱 FOR 子句 (Transact-SQL)For syntax and usage, see FOR Clause (Transact-SQL).

選項 2 - SELECT 陳述式使用 FOR JSON AUTO 控制輸出Option 2 - SELECT statement controls output with FOR JSON AUTO

AUTO 模式中,SELECT 陳述式的結構決定 JSON 輸出的格式。In AUTO mode, the structure of the SELECT statement determines the format of the JSON output.

根據預設, null 值不會包含在輸出中。By default, null values are not included in the output. 您可以使用 INCLUDE_NULL_VALUES 來變更此行為。You can use the INCLUDE_NULL_VALUES to change this behavior.

以下是使用 AUTO 模式搭配 FOR JSON 子句的範例查詢。Here's a sample query that uses AUTO mode with the FOR JSON clause.

SELECT name, surname  
FROM emp  
FOR JSON AUTO;

這是傳回的 JSON。And here is the returned JSON.

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

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

下列 SELECT...FOR JSON AUTO 範例會顯示當已 JOIN 的資料表資料間有 1:Many 關聯性時,JSON 結果的外觀。The following example of SELECT...FOR JSON AUTO includes a display of what the JSON results look like when there is a 1:Many relationship between data from JOIN'ed tables.

也會示範傳回的 JSON 中沒有 Null 值。The absence of the null value from the returned JSON is also illustrated. 不過,您可以在 FOR 子句中使用 INCLUDE_NULL_VALUES 關鍵字來覆寫此預設行為。However, you can override this default behavior by use of the INCLUDE_NULL_VALUES keyword on the FOR clause.

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
   --where
   --   c.ClassName LIKE '%Math%'
   order by
      c.ClassName,
      s.StudentName
   FOR
      JSON AUTO
      --, INCLUDE_NULL_VALUES
;

go

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

go

接下來是前述 SELECT 輸出的 JSON。And next is the JSON that is output by the preceding SELECT.

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 的詳細資訊More info about FOR JSON AUTO

如需詳細資訊和範例,請參閱使用 AUTO 模式自動格式化 JSON 輸出 (SQL Server)For more detailed info and examples, see Format JSON Output Automatically with AUTO Mode (SQL Server).

如需語法和使用方式,請參閱 FOR 子句 (Transact-SQL)For syntax and usage, see FOR Clause (Transact-SQL).

控制其他 JSON 輸出選項Control other JSON output options

使用下列其他選項控制 FOR JSON 子句的輸出。Control the output of the FOR JSON clause by using the following additional options.

FOR JSON 子句的輸出Output of the FOR JSON clause

FOR JSON 子句的輸出具有下列特性:The output of the FOR JSON clause has the following characteristics:

  1. 結果集包含單一資料行。The result set contains a single column.

    • 小型結果集會包含單一資料列。A small result set may contain a single row.
    • 大型結果集跨多個資料列分割太長的 JSON 字串。A large result set splits the long JSON string across multiple rows.
      • 根據預設,SQL Server Management Studio (SSMS) 會在輸出設定為 [以方格顯示結果] 時,將結果串連成單一資料列。By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. SSMS 的狀態列會顯示實際資料列計數。The SSMS status bar displays the actual row count.
      • 其他用戶端應用程式可能需要程式碼,藉由串連多個資料列的內容,來將較長的結果重新合併成有效的單一 JSON 字串。Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. 如需這個程式碼在 C# 應用程式中的範例,請參閱在 C# 用戶端應用程式中使用 FOR JSON 輸出For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

    FOR JSON 輸出的範例

  2. 結果會格式化為 JSON 物件陣列。The results are formatted as an array of JSON objects.

    • JSON 陣列中的元素數目等於 SELECT 陳述式結果中的資料列數目 (在套用 FOR JSON 子句之前)。The number of elements in the JSON array is equal to the number of rows in the results of the SELECT statement (before the FOR JSON clause is applied).

    • SELECT 陳述式 (在套用 FOR JSON 子句之前) 結果中的每個資料列會成為陣列中的個別 JSON 物件。Each row in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a separate JSON object in the array.

    • SELECT 陳述式 (在套用 FOR JSON 子句之前) 結果中的每個資料行則會成為 JSON 物件的屬性。Each column in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a property of the JSON object.

  3. 資料行名稱與其值會根據 JSON 語法逸出。Both the names of columns and their values are escaped according to JSON syntax. 如需詳細資訊,請參閱 FOR JSON 如何逸出特殊字元和控制字元 (SQL Server)For more info, see How FOR JSON escapes special characters and control characters (SQL Server).

範例Example

以下是示範 FOR JSON 子句如何格式化 JSON 輸出的範例。Here's an example that demonstrates how the FOR JSON clause formats the JSON output.

查詢結果Query results

AA BB CC DD
1010 1111 1212 XX
2020 2121 2222 YY
3030 3131 3232 ZZ
       

JSON 輸出JSON output

[{
    "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 子句輸出中所看到之項目的詳細資訊,請參閱下列主題。For more info about what you see in the output of the FOR JSON clause, see the following topics.

深入了解 SQL Server 和 Azure SQL Database 中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 影片Microsoft videos

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介,請參閱下列影片:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

另請參閱See Also

FOR 子句 (Transact-SQL) FOR Clause (Transact-SQL)
在 SQL Server 和用戶端應用程式中使用 FOR JSON 輸出 (SQL Server)Use FOR JSON output in SQL Server and in client apps (SQL Server)