Sql-Server

為什麼 SQL Server 將它的 (JSON) 響應拆分為多行？

  • November 13, 2020

我正在嘗試建構一個導致 SQL Server 生成的 JSON 對象的查詢。我發現我可以使用子查詢來使用包含問題列表的 JSON 字元串填充欄位（在本例中為問題欄位）。

以下是查詢：

SELECT
   quizzes.id AS 'id',
   quizzes.name AS 'name',
   quizzes.description AS 'description',
   quizzes.instructions AS 'instructions',
   author.id AS 'author.id',
   author.midas AS 'author.midas',
   author.first_name AS 'author.first_name',
   author.last_name AS 'author.last_name',
   author.email AS 'author.email',
   author.tel AS 'author.tel',
   author.department_name AS 'author.department_name',
   author.created_at AS 'author.created_at',
   author.last_updated AS 'author.last_updated',
   course.id AS 'course.id',
   course.name AS 'course.name',
   course.description AS 'course.description',
   course.crn AS 'course.crn',
   instructor.id AS 'course.instructor.id',
   instructor.midas AS 'course.instructor.midas',
   instructor.first_name AS 'course.instructor.first_name',
   instructor.last_name AS 'course.instructor.last_name',
   instructor.email AS 'course.instructor.email',
   instructor.tel AS 'course.instructor.tel',
   instructor.department_name AS 'course.instructor.department_name',
   instructor.created_at AS 'course.instructor.created_at',
   instructor.last_updated AS 'course.instructor.last_updated',
   course.created_at AS 'course.created_at',
   course.last_updated AS 'course.last_updated',
   
   
   (
       SELECT
           questions.id AS 'id',
           questions.text AS 'text',
           question_types.id AS 'type.id',
           question_types.name AS 'type.name',
           question_types.created_at AS 'type.created_at',
           question_types.description AS 'type.description',
           question_author.id AS 'author.id',
           question_author.midas AS 'author.midas',
           question_author.first_name AS 'author.first_name',
           question_author.last_name AS 'author.last_name',
           question_author.email AS 'author.email',
           question_author.tel AS 'author.tel',
           question_author.department_name AS 'author.department_name',
           question_author.created_at AS 'author.created_at',
           question_author.last_updated AS 'author.last_updated',
           questions.is_graded AS 'is_graded',
           questions.score_value AS 'score_value',
           questions.created_at AS 'created_at',
           questions.last_updated AS 'last_updated'
       FROM
           questions
       LEFT JOIN users AS question_author ON question_author.id = questions.author
       LEFT JOIN question_types ON question_types.id = questions.type
       WHERE
           questions.quiz = quizzes.id FOR JSON PATH, INCLUDE_NULL_VALUES
   ) AS 'questions',
   
   
   quizzes.created_at AS 'created_at',
   quizzes.last_updated AS 'last_updated'
FROM
   quizzes
   LEFT JOIN users AS author ON quizzes.author = author.id
   LEFT JOIN courses AS course ON quizzes.course = course.id
   LEFT JOIN users AS instructor ON course.instructor = instructor.id FOR JSON PATH,
   INCLUDE_NULL_VALUES;

問題是：當我執行此查詢時，它會以兩行響應，其中生成的 JSON 字元串一分為二。顯然這是不可取的。

經過調查，我發現如果我刪除LEFT JOIN‘s，那麼查詢會按應有的方式響應（只有一行，整個字元串完好無損）。

以下是以下行為的範例：

SELECT n = sc1.name FROM sys.syscolumns sc1 FOR JSON AUTO

如上所示，正在返回 11 行。

結果

JSON 輸出的長度約為 20,000 個字元。

JSON 長度

我正在使用以下版本的 SQL Server

Microsoft SQL Server 2019 - 15.0.4073.23 (X64) 
Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

為什麼會這樣？我該如何解決？

經過進一步研究，我從這篇 StackOverflow 文章中發現 SQL Server 將FOR JSON查詢分解為“~2kb 塊”。

Sql Server 將 FOR JSON 查詢的結果拆分為 ~2KB 塊，因此您應該像 MSDN 頁面上那樣連接片段，或者您可以將結果流式傳輸到某個輸出流中。

這意味著每個塊只能發送約 2000 個字元。

更新：

在Max Vernon和Andriy M的幫助下，我們找到了一個相當簡單的解決方案。

DECLARE @json nvarchar(max);

;WITH src (n) AS
(
   SELECT n = sc1.name
   FROM sys.syscolumns sc1
   FOR JSON AUTO
)
SELECT @json = src.n
FROM src

SELECT @json, LEN(@json);

導致此的聊天可以在這裡找到。

上面的查詢返回兩列。

  1. 完全組裝的 JSON 字元串
  2. 該字元串的長度

理想情況下，您可以用自己的查詢替換兩個括號之間的查詢。

為什麼這行得通？

根據微軟的文件

SQL Server 使用此行集的預定義列名，其中一列類型為 NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” - 以 UTF-16 編碼指示分塊的 XML 行集。這需要 API 對 XML 塊行集進行特殊處理，以在客戶端將其公開為單個 XML 實例。在 ADO.Net 中需要使用 ExecuteXmlReader，而在 ADO/OLEDB 中則需要使用 ICommandStream 介面。

（雖然上面提到的確實是XML，但JSON也是如此。）

JSON 和 XML 響應以塊的形式返回的原因首先是出於性能原因：

**對於最大 XML$$ JSON $$XML 的發布性能$$ JSON $$對生成的行集進行流式 XML 格式處理，並將其輸出以小塊的形式直接發送到伺服器端 TDS 程式碼，而無需在伺服器空間中緩衝整個 XML。**塊大小為 2033 個 UCS-2 字元。因此，大於 2033 個 UCS-2 字元的 XML 以多行的形式發送到客戶端，每行包含一個 XML 塊。

上述解決方案通過FOR JSON首先將結果設置為變數，然後發送變數的值來規避這一點，這導致 SQL Server 將響應作為一行返回給客戶端。

應該注意的是，一些數據庫客戶端（特別是 SQL Server Management Studio）能夠“重建”分塊響應，但如果您使用 PHP ( PDO ) 或在免費試用客戶端（例如 Mac 的 TablePlus）上執行，您將看到原始的分塊響應。

表現

就性能而言，我沒有進行任何廣泛的測試，但我可以從我所做的有限測試中提供以下數據：

使用執行最新作業系統的 MacBook Pro，我發現平均以下查詢

SELECT n = sc1.name
   FROM sys.syscolumns sc1
   FOR JSON AUTO

將在大約23,300μs 內處理

而查詢

DECLARE
   @json nvarchar (max);

;WITH src (n) AS
(
   SELECT n = sc1.name
   FROM sys.syscolumns sc1
   FOR JSON AUTO
)
SELECT @json = src.n
FROM src

SELECT @json, LEN(@json);

平均花費137.8μs 的處理時間。

這似乎與文件所說的直接衝突，所以我不確定這些結果的可信度。但是，您自己對此進行測試可能是值得的。

測試數據結果

引用自：https://dba.stackexchange.com/questions/279613

相關問答

Sql-Server

SQL 在更新查詢中按子查詢插入

  • November 11, 2021
檢視問答
Sql-Server

按位列分組

  • August 9, 2021
檢視問答
Sql-Server

Sql Server 字元串 concat 與 order by 和子查詢

  • February 25, 2020
檢視問答
Sql-Server

根據同一表中的另一個值從具有特定值的表中選擇行

  • February 19, 2020
檢視問答
Sql-Server

對另一個查詢的結果執行查詢？

  • January 16, 2020
檢視問答
Sql-Server

查詢獲取數據庫的物理文件名

  • July 26, 2019
檢視問答