作者：唐瑤瑤 / 臺灣大學計算機及資訊網路中心程式設計師
JSON(JavaScript Object Notation)為衍生自JavaScript的輕量級文字資料格式，容易閱讀及處理，因此在開發網頁應用程式時，可以很方便的交換資料或儲存於資料庫中。
網頁表單使用JavaScript收集資料，經由AJAX將資料送到伺服器處理後儲存至資料庫，若整個使用JSON串起來十分簡潔。Microsoft直到SQL Server 2016 (13.x)版開始支援JSON格式資料處理，本文將幾個應用介紹給大家。
下面這張流程圖闡述SQL剖析JSON函數：ISJSON()、JSON_VALUE()、JSON_QUERY()，OPENJSON將整串JSON文字轉換成關聯式資料表後儲存，最後則是將Transact-SQL查詢後的結果以FOR JSON傳回。
範例一：網頁表單Form收集使用者輸入之資料處理
JavaScript Code
$("#myform").submit(function (event) {
event.preventDefault();
const form = $(event.target);
const array = $(form).serializeArray();
const json = {};
$.each(array, function () {
json[this.name] = this.value || "";
});
$.post("xxxx.aspx", { 'formstr': JSON.stringify(json)} ).done(
function (result) {
$('#myAlert').show('fade');
setTimeout(function () {$('#myAlert').hide('fade');
}, 5000)
});
});
上述Ajax post回伺服器處理的formstr內容如下：
formstr JSON
{"regno":"A1234567","year":"111","semester":"2","comment":"昔人已乘黃鶴去，此地空餘黃鶴樓。\n黃鶴一去不復返，白雲千載空悠悠。\n晴川歷歷漢陽樹，芳草萋萋鸚鵡洲。\n日暮鄉關何處是？煙波江上使人愁。","pscomment":"朝代：唐代++作者：崔顥"}
我習慣使用MSSQL stored procedure來執行所有SQL querys，伺服器端將formstr當作輸入參數@json執行 [dbo].[AddSomeComment]。
首先利用ISJSON(@json) >0判斷@json是否為JSON text，接著使用JSON_VALUE(@json, path) 取值後刪除對應之紀錄並重新insert record。參數path的寫法類似JavaScript，請參考程式碼。這裡要注意的是JSON_VALUE ()只能取純值value，也就是文字或數字，若對應path之值為陣列或物件，則需要使用JSON_QUERY()。
MSSQL Code
ALTER PROCEDURE [dbo].[AddSomeComment]
@json nvarchar(max)
AS
BEGIN
if (ISJSON(@json)) > 0
begin
delete [dbo].somecomment where reg_no = JSON_VALUE(@json, '$.regno') and year=JSON_VALUE(@json, '$.year') and semester=JSON_VALUE(@json, '$.semester')
INSERT INTO [dbo].somecomment
(reg_no ,
[year],
semester,
comment,
pscomment)
SELECT JSON_VALUE(@json, '$.regno') as reg_no,
JSON_VALUE(@json, '$.year') AS year,
JSON_VALUE(@json, '$.semester') AS semester,
JSON_VALUE(@json, '$.comment') AS comment,
JSON_VALUE(@json, '$.pscomment') AS pscomment
end
END
完成資料之儲存後，接著來看如何Select取出JSON文件。同樣以SQL Stored Procedure來說明，參數部分要加上@jsonOutput NVARCHAR(MAX) OUTPUT作為輸出。
SET @jsonOutput最後需加上FOR JSON PATH，產生之@jsonOutput如下：
[{"no":1,"reg_no":"A1234567","comment":"昔人已乘黃鶴去，此地空餘黃鶴樓。\n黃鶴一去不復返，白雲千載空悠悠。\n晴川歷歷漢陽樹，芳草萋萋鸚鵡洲。\n日暮鄉關何處是？煙波江上使人愁。","pscomment":"朝代：唐代 作者：崔顥","year":"111","semester":"2","name":"王大大","account":"wangdada"}]
第14行註解WITHOUT_ARRAY_WRAPPER，若移除註解則會直接移除@jsonOutput最外層之陣列包裝。
MSSQL Code
ALTER PROCEDURE [dbo].[QueryComment]
@RegNo varchar(9),@year varchar(3),@semester char(1),
@jsonOutput NVARCHAR(MAX) OUTPUT
AS
BEGIN
SET @jsonOutput = (
SELECT ROW_NUMBER() OVER(ORDER BY Reg_no ) no,
[reg_no]
,[comment]
,isnull([pscomment],'') as [pscomment]
FROM [dbo].[comment_view]
where reg_no = @RegNo and [year]=@year and semester=@semester
FOR JSON PATH)
--, WITHOUT_ARRAY_WRAPPER)
END
範例二：行事曆ics檔
行事曆ics檔可以使用nodejs轉換成JSON格式的文件，下面就利用OPENJSON來將JSON文件轉入資料庫儲存，以方便我們使用。
行事曆JSON 格式
DECLARE @json NVARCHAR(MAX)
SET @json = N'[
{
"month": "2023-07",
"days": [
{
"day": "01",
"wk": 6,
"events": [
"112學年度學士班二年級轉學生招生筆試",
"Saturday, July 1 Academic Year 2023-2024 Enroll Examination for sophomore transfer students"
]
},
{
"day": "03",
"wk": 1,
"events": [
"應屆畢業生補考開始(至4日止)",
"Monday, July 3 Make-up examinations for students graduating in the current academic year begin (end on July 4) "
]
},
{
"day": "31",
"wk": 1,
"events": [
"111學年度第二學期結束",
" 碩、博士班生學位考試完畢",
"Monday, July 31 Spring semester ends ",
"Monday, July 31 Masters/Doctoral thesis defense ends"
] } ] },
-------------------------------------------省略---------------------------------
{
"month": "2024-07",
"days": [
{
"day": "06",
"wk": 6,
"events": [
"113學年度學士班二年級轉學生招生筆試",
"Saturday, July 6 Academic Year 2024-2025 Enroll Examination for sophomore transfer students"
]
},
{
"day": "31",
"wk": 3,
"events": [
"112學年度第二學期結束",
"碩、博士班生學位考試完畢",
"Wednesday, July 31 Spring semester ends ",
"Wednesday, July 31 Masters/Doctoral thesis defense ends"
] } ]
} ]'
Sql Query Code
select month, day ,weekday, event FROM OPENJSON(@json) WITH (
month NVARCHAR(7) '$.month',
days NVARCHAR(MAX) '$.days' AS JSON
)
OUTER APPLY OPENJSON(days) WITH (
day NVARCHAR(MAX) '$.day',
weekday NVARCHAR(MAX) '$.wk',
events NVARCHAR(MAX) '$.events' AS JSON
)
OUTER APPLY OPENJSON(events) WITH (
event NVARCHAR(MAX) '$'
)
利用上述這段SQL Query code，我們得以產生資料表如下圖，也就是以一個event為一筆紀錄。
iNTU行事曆即是以此模式顯示。
參考URL：