在SQL Server中操作JSON資料

JSON(JavaScript Object Notation)最早是1999年設計於JavaScript語言中，因為其簡潔的語法格式，利用Name/Value的結構，現今已成為在XML格式之外另一種常見的資料格式語法，許多開發工具的設定檔，從過去的XML格式，很多都已經改以JSON格式，像是廣受程式設計師愛用的Visual Studio Code，設定檔launch.json就是JSON格式。

{
    "version": "0.2.0",
    "configurations": [
        {
            "type": "pwa-chrome",
            "request": "launch",
            "name": "Open h1.html",
            "file": "d:\\temp\\VSCodeData\\h1.html"
        }
    ]
}     

相較於xml格式的資料結構

<?xml version="1.0" ?>
<configurations>
    <type>pwa-chrome</type>
    <request>launch</request>
    <name>Open h1.html</name>
    <file>d:\\temp\\VSCodeData\\h1.html</file
</configurations>     

除去開始標記(Opening Tag)以及結束標記(Closing Tag)的方式

<type>pwa-chrome</type>     

改以name:value形式描述資料內容

"type": "pwa-chrome",   

資料表現更為精簡，在傳輸資料時檔案更小，也有利於傳輸的速度。 SQL Server 2016之後也加入了對JSON格式的支援，不過並沒有設計xml資料型別，JSON格式是以varchar, nvarchar, char, nchar, varchar(max), nvarchar(max)形式儲存於資料庫中。以下介紹幾個SQL Server中JSON相關語法及函數。

建立JSON資料：(SQL Server 2022以後的版本支援)

SELECT JSON_OBJECT('name':'value', 'type':1)   

結果

JSON陣列：(SQL Server 2022以後的版本支援)

SELECT JSON_ARRAY(
    JSON_OBJECT('name':'A-Product', 'type':1), 
    JSON_OBJECT('name':'B-Product', 'type':1)
)             

結果

查詢JSON資料
JSON_QUERY(), JSON_VALUE()：SQL Server 2016以後支援

DECLARE @jsonInfo NVARCHAR(MAX)=
N'{
    "info": {
        "type": 1,
        "address": {
            "town": "Bristol",
            "county": "Avon",
            "country": "England"
        },
        "tags": ["Sport", "Water polo"]
    },
    "type": "Basic"
}';


SELECT JSON_QUERY(@jsonInfo,'$.info.tags');

SELECT JSON_VALUE(@jsonInfo,'$.info.tags[0]');               

執行結果

因為JSON格式的精簡，檔案大小也遠小於XML格式，許多交換格式已逐漸由JSON格式取代原有XML，JSON格式也成了開發系統時不可或缺的技術。SQL Server 2016起加入不少相關語法與支援，由關聯式資料表型式轉為JSON格式(FOR JSON)，以及由JSON格式轉為關聯式資料表型式，如以下語法：

SELECT * FROM  
OPENJSON ( @json )  
WITH (   
                Number   varchar(200) '$.Order.Number' ,  
                Date     datetime     '$.Order.Date',  
                Customer varchar(200) '$.AccountNumber',  
                Quantity int          '$.Item.Quantity'  
);                  

善用這些SQL Server中的功能，可以在開發系統時事半功倍，避免一些不必要的程式碼。