解決 SQL Server 中的 JSON 常見問題

發行項
05/04/2023

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

在此處尋找關於 SQL Server 中內建 JSON 支援的一些常見問題解答。

FOR JSON 和 JSON 輸出

FOR JSON PATH 或 FOR JSON AUTO？

問：我想要透過單一資料表上的簡易 SQL 查詢，建立 JSON 文字結果。 FOR JSON PATH 與 FOR JSON AUTO 產生相同的輸出。我該使用哪一個選項？

答：使用 FOR JSON PATH。 JSON 輸出並無任何差異，但 AUTO 模式會套用某些額外邏輯，其可檢查是否應將資料行執行巢狀處理。請考慮使用 PATH 做為預設選項。

建立巢狀的 JSON 結構

問：我想要在相同層級產生具有數個陣列的複雜 JSON。 FOR JSON PATH 可使用路徑建立巢狀物件，而 FOR JSON AUTO 會針對每個資料表建立額外巢狀層級。這兩個選項都無法產生我想要的輸出。如何建立現有選項未直接支援的自訂 JSON 格式？

答：您可以新增 FOR JSON 查詢作為傳回 JSON 文字的資料行運算式，以建立任何資料結構。您也可以使用 JSON_QUERY 函數手動建立 JSON。下列範例會示範這些技術。

SELECT col1, col2, col3,  
     (SELECT col11, col12, col13 FROM t11 WHERE t11.FK = t1.PK FOR JSON PATH) as t11,  
     (SELECT col21, col22, col23 FROM t21 WHERE t21.FK = t1.PK FOR JSON PATH) as t21,  
     (SELECT col31, col32, col33 FROM t31 WHERE t31.FK = t1.PK FOR JSON PATH) as t31,  
     JSON_QUERY('{"'+col4+'":"'+col5+'"}') as t41  
FROM t1  
FOR JSON PATH

資料行運算式中的所有 FOR JSON 查詢或 JSON_QUERY 函數結果，皆會格式化為個別的巢狀 JSON 子物件並包含於主要結果。

避免在 FOR JSON 輸出中產生雙逸出 JSON

問：我已將 JSON 文字儲存於資料表資料行。我想要將其包含在 FOR JSON 輸出中。 FOR JSON 會逸出 JSON 中的所有字元，因此我會收到 JSON 字串而非巢狀物件，如下列範例所示。

SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH

此查詢會產生下列輸出。

[{"myText":"Text", "myJson":"{\"day\":23}"}]

如何防止此行為？我想讓 {"day":23} 以 JSON 物件形式傳回而非逸出的文字。

答：儲存於文字資料行的 JSON 或常值，其處理方式與任何文字相似。也就是說，它會以雙引號括住並逸出。若您想要傳回未逸出的 JSON 物件，則必須將 JSON 資料行以引數形式傳送至 JSON_QUERY 函數，如下列範例所示。

SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH

JSON_QUERY 若無選用的第二參數，則僅會傳回第一個引數做為結果。由於 JSON_QUERY 永遠會傳回有效的 JSON，因此 FOR JSON 知道此結果無須逸出。

使用 WITHOUT_ARRAY_WRAPPER 子句產生的 JSON，會在 FOR JSON 輸出中逸出

問：我嘗試使用 FOR JSON 和 WITHOUT_ARRAY_WRAPPER 選項，格式化資料行運算式。

SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH

FOR JSON 查詢傳回的文字似乎會逸出為純文字。僅在指定 WITHOUT_ARRAY_WRAPPER 時才會發生此情況。為何不會將它視為 JSON 物件，並將它以未逸出方式包含在結果中？

答：如果您在內部 FOR JSON 中指定 WITHOUT_ARRAY_WRAPPER 選項，產生的 JSON 文字不一定是有效的 JSON。因此，外部 FOR JSON 會假定此為純文字並逸出字串。若您確定 JSON 輸出有效，請使用 JSON_QUERY 函數將其包裝以升級為正確格式的 JSON，如下列範例所示。

SELECT 'Text' as myText,  
      JSON_QUERY((SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as myJson  
FOR JSON PATH

OPENJSON 和 JSON 輸入

使用 OPENJSON 從 JSON 文字傳回巢狀 JSON 子物件

問：無法使用具明確結構描述的 OPENJSON，開啟包含純量值、物件和陣列的複雜 JSON 物件陣列。參考 WITH 子句中的索引鍵時，僅會傳回純量值。物件和陣列會以 Null 值形式傳回。如何以 JSON 物件的形式擷取物件或陣列？

答：若您想要傳回物件或陣列作為資料行，請在資料行定義中使用 AS JSON 選項，如下列範例所示。

SELECT scalar1, scalar2, obj1, obj2, arr1  
FROM OPENJSON(@json)  
    WITH ( scalar1 int,  
        scalar2 datetime2,  
        obj1 NVARCHAR(MAX) AS JSON,  
        obj2 NVARCHAR(MAX) AS JSON,  
        arr1 NVARCHAR(MAX) AS JSON)

使用 OPENJSON 傳回長文字值，而不要用 JSON_VALUE

問：在包含長文字的 JSON 文字當中具有描述索引鍵。 JSON_VALUE(@json, '$.description') 傳回 NULL 而非值。

答： JSON_VALUE 設計為傳回小純量值。函數通常會傳回 NULL 而非溢位錯誤。若您想要傳回整數值，請使用支援 NVARCHAR(MAX) 值的 OPENJSON，如下列範例所示。

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')

使用 OPENJSON 處理重複的索引鍵，而不要用 JSON_VALUE

問：在 JSON 文字中具有重複的索引鍵。 JSON_VALUE 僅傳回在路徑上找到的第一個索引鍵。如何傳回所有具相同名稱的索引鍵？

答：內建 JSON 純量函數僅會傳回第一次出現的參考物件。若您需要多個索引鍵，請使用 OPENJSON 資料表值函式，如下列範例所示。

SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'

OPENJSON 需要相容性層級 130

問：我嘗試在 SQL Server 2016 中執行 OPENJSON，結果收到下列錯誤。

Msg 208, Level 16, State 1 'Invalid object name OPENJSON'

答： OPENJSON 函式僅適用於相容性等級 130。若您的資料庫相容性等級低於 130，則會隱藏 OPENJSON。其他 JSON 函數適用於所有的相容性層級。

其他問題

在 JSON 文字中包含非英數字元的參考索引鍵

問：在 JSON 文字的索引鍵中具有非英數字元。如何參考這些屬性？

答：您必須在 JSON 路徑中使用引號將其括住。例如： JSON_VALUE(@json, '$."$info"."First Name".value') 。

深入了解 SQL Server 和 Azure SQL Database 中的 JSON

Microsoft 影片

注意

本節中的部分影片連結目前可能無法運作。 Microsoft 正在將先前在 Channel 9 上的內容移轉至新的平台。我們會在影片移轉至新平台時更新連結。

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

NoSQL 與關聯式領域之間的橋樑 JSON