UCOM 職能補給站

提供您專業技能的最佳管道，IT企業主管唯一推薦

2025年5月29日 星期四

SQL Server和 JSON資料型別


作者：楊先民  
精誠資訊/恆逸教育訓練中心資深講師


 

JSON 是種熱門的文字資料格式，用於在新式 Web 和行動應用程式中交換資料，它的全名是JavaScript Object Notation。 可用於將非結構化的資料儲存在記錄檔或是類似 Microsoft Azure Cosmos DB的 NoSQL 資料庫中。
許多 REST Web 服務會傳回已格式化為 JSON 文字的結果，或接受已格式化為 JSON 的資料。例如，大部分的 Azure 服務 (例如 Azure 搜尋服務、Azure 儲存體和 Azure Cosmos DB) 都具有傳回或取用JSON的 REST 端點。
JSON 也是用於透過 AJAX 呼叫在網頁和 Web 伺服器之間交換資料的主要格式。

 

首先在SQL Server 2016 推出的 JSON 函數可讓使用者將 NoSQL 與關聯式概念結合在同一個資料庫中。 您可以將傳統關聯式資料行與包含採用 JSON 文字格式之文件的資料行結合在同一個資料表中、剖析並匯入關聯式結構中的 JSON 文件，或讓關聯式資料採用 JSON 文字格式。

下面是 JSON的範例：

[

    {

        "name": "John",

        "skills": [ "SQL", "C#", "Azure" ]

    },

    {

        "name": "Jane",

        "surname": "Doe"

    }

]


您可使用 SQL Server 內建函式和運算子，以 JSON 文字執行下列作業：

  • 剖析 JSON 文字，讀取或是修改其值。
  • 將 JSON 物件的陣列轉換成資料表格式。
  • 在已轉換的 JSON 物件上執行任何 Transact SQL 查詢。
  • 以 JSON 格式格式化 Transact-SQL 查詢的結果。

SQL Server 和 SQL Database 的主要 JSON 功能

JSON 資料型別

相較於以 varchar/nvarchar 儲存 JSON 資料，以原生二進位格式儲存 JSON 文件的新 json 資料類型，可提供下列優點：

  • 更有效率的讀取，因為已將文件剖析
  • 更有效率的寫入，因為查詢可以更新個別值，而不需要存取整份文件
  • 更有效率的儲存體，已針對壓縮進行最佳化
  • 與現有程式碼的相容性沒有變更

這個其實和 xml 相同，之前xml的資料也是儲存在 varchar這種類型的資料型別，後來才有所謂的 xmL資料型別， jason 目前則是一開始有函數，之後才有 json 資料型別（也就是2025的 sql server將支援 json資料型別）

從 JSON 文字中擷取值，然後在查詢中使用它們

如有儲存在資料庫資料表中的 JSON 文字，您可以使用下列內建函式來讀取或修改 JSON 文字中的值：

在下列範例中，查詢會使用來自資料表 People 的關聯式資料和 JSON 資料 (儲存在名為 jsonCol 的資料行中)：

SELECT Name,

    Surname,

    JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,

    JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')

        + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,

    JSON_QUERY(jsonCol, '$.info.skills') AS Skills

FROM People

WHERE ISJSON(jsonCol) > 0

    AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'

    AND STATUS = 'Active'

ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

應用程式和工具分辨不出從純量資料表資料行取得的值和從 JSON 資料行取得的值有何不同。 您可以在 Transact-SQL 查詢的任意部分 (包括 WHERE、ORDER BY 或 GROUP BY 子句、範圍彙總等等)，使用來自 JSON 文字的值。 JSON 函式會使用類似 JavaScript 的語法來參考 JSON 文字內的值。

變更 JSON 值

如果您必須修改部分 JSON 文字，可以使用 JSON_MODIFY (Transact-SQL) 函式來更新 JSON 字串中的屬性值，並傳回更新的 JSON 字串。 下列範例示範在包含 JSON 的變數中更新屬性的值：

DECLARE @json NVARCHAR(MAX);


SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';

SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT modifiedJson = @json;

將 JSON 集合轉換為資料列集

您不需要自訂查詢語言也能在 SQL Server 中查詢 JSON。 您可以使用標準的 T-SQL 查詢 JSON 資料。 如果您必須建立 JSON 資料的查詢或報表，可以呼叫 OPENJSON 資料列集函式，輕鬆地將 JSON 資料轉換成資料列和資料行。 如需詳細資訊，請參閱使用 OPENJSON 剖析和轉換 JSON 資料。

下列範例示範呼叫 OPENJSON，並將儲存於 @json 變數中的物件陣列轉換為可使用標準 Transact-SQL SELECT 陳述式查詢的資料列集︰

DECLARE @json NVARCHAR(MAX);


SET @json = N'[

  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},

  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}

]';


SELECT *

FROM OPENJSON(@json) WITH (

    id INT 'strict $.id',

    firstName NVARCHAR(50) '$.info.name',

    lastName NVARCHAR(50) '$.info.surname',

    age INT,

    dateOfBirth DATETIME2 '$.dob'

);

將 SQL Server 資料轉換為 JSON 或匯出 JSON

將 FOR JSON 子句加入至 SELECT 陳述式，以將 SQL Server 資料或 SQL 查詢結果格式化為 JSON。 使用 FOR JSON 將您用戶端應用程式的 JSON 輸出格式設定委派給 SQL Server。 如需詳細資訊，請參閱使用 FOR JSON 將查詢結果格式化為 JSON。

下列範例示範搭配 FOR JSON 子句使用 PATH 模式：

SELECT id,

    firstName AS "info.name",

    lastName AS "info.surname",

    age,

    dateOfBirth AS dob

FROM People

FOR JSON PATH;

有沒有發現，其實這些做法，和 xml 的使用方式幾乎一模一樣，但差別在於，以前 xml 尚未有 w3c的標準時，微軟有它自己的函數以及做法，2005年之後，又加入了 w3c時的處理方式搞的非常凌亂，感覺有兩套作法，而 json 不同，一開始就沒有所謂的微軟本家的作法，直接就是目前的工業標準處理方式，所以和之前的 xml 相對照起來，會變的比較容易。

 


, , 0

0 意見:

張貼留言