SQL Server 2016中的 JSON使用

by adonis 19. 一月 2017 14:21

作    者:楊先民
審    稿:張智凱

 

1    前言


SQL Server 2016之後,微軟開始支援了 JSON這個東西,本期就來介紹一下,什麼是 JSON,以及 SQL 2016對 JSON大概有哪些有用的支援吧。

 

2    JSON 是什麼

以前要進行資料交换時,最常用的手法是使用 Web Service,利用交换 XML資料讓兩個不同平台能夠彼此了解自己。

像我有寫過一支上傳油耗的 iOS程式,其中車主可以把油耗資訊上傳到我在後端架的一台 SQL Server上,然而我在 iOS所使用的資料庫是 sqlite,使用的語言是 object-c,似乎是不太容易能夠尋找一個資料庫的 API能夠直連我的後端 SQL Server,就算是有…呃,是在 Internet的環境之下啊…

所以我採用的方式是在 SQL Server端寫一個 web service,利用 web service接收函數,將數據資料寫入到 SQL Server中,而因為 Web Service是跨平台的,所以 iphone所寫的程式也可以呼叫。

其實在 web service底層的原理就是 xml,為什麼這麼說呢?因為在 iphone中呼叫 web service的方式竟然是要自己控制 xml的資料,喔我的天,自此之後我才知道原來微軟的開發如此的簡單,因為它把很多東西都包起來,所以你不用看見赤裸裸的 xml,感覺是用一般的方式呼叫函式,其實它在底層幫你操作那些複雜的xml。

所以在 iphone開發中,還有另一種流派,就是用簡易版的 xml,也就是我們今天講的主題:JSON,利用控制 JSON來達成資料交换的目的。
JSON是JavaScript Obect Notation的縮寫,是一種輕量級的資料交換語言(重量級應該就是指xml吧),它以文字為基礎,易於讓人閱讀是其特色。

JSON用來描述資料結構,以下面的方式存在:
.物件(object):一個物件以 { 開始 ,以 } 結束,在物件中包含了一連串的 名稱/值,中間用 , 分隔。
.名稱/值:名稱和值之間用 : 隔開,使用的方式是 {name:value}
.值的陣列(Array),使用中括號[],中間用 , 分隔,看起來就像這樣:
[collection,collection]

.字串:以雙引號 "" 括起來的字元。
.數字:0-9的數字,可以是負數或小數,也可以使用指數 e。

以下的樣子就是 JSON看起來的樣子:
[  
   { "name": "John", "skills":["SQL","C#","Azure"] }, 
   { "name": "Jane", "surname": "Doe" } 
]

3    SQL 2016對於 JSON的支援


說到 SQL Server 2016對於 JSON的支援,其實感覺和對 XML的支援是有點相像,只是並不像 XML有 XML資料型別的提供,JSON的資料是放在 varchar(MAX)的資料型別中。

你同樣可以把資料表轉成 JSON,也可以把 JSON的文字資料轉成資料表的方式呈現(其實是有利於匯入資料)。

先來一個簡單的例子吧,你可以利用ISJSON的函數來判斷這個字串是不是 JSON:

我們把一段 JSON文件丟入變數,並且使用 ISJSON函數來確認:

declare @x nvarchar(max)='{
  "Items": [
    {
      "ItemID": 101,
      "ItemDesc": "Monitor CRT 18\"",
      "Unitprice": 200
    },
    {
      "ItemID": 110,
      "ItemDesc": "Printer Catridge",
      "Unitprice": 112
    },
    {
      "ItemID": 112,
      "ItemDesc": "Copier Ink",
      "Unitprice": 20
    },
    {
      "ItemID": 123,
      "ItemDesc": "Wireless Mouse",
      "Unitprice": 30
    }
  ]
}'

SELECT ISJSON(@x)

你可以發現 @x變數是設定成 varchar(MAX),利用 ISJSON函數來判斷傳進來的參數是否是 JSON,以本例而言應該會傳回 1。

而事實上 SQL Server 2016對 JSON的支援大概分幾個部分:

1.    剖析 JSON字串,讀以及修改資料
2.    把 JSON陣列轉成資料表格式
3.    使用 T-SQL指令轉換 JSON物件
4.    利用 T-SQL指令改變 JSON的格式

我對於在 SQL Server內去修改 JSON或是 XML的資料一直抱著懷疑的態度,主要是因為我覺得大多數的應用就是利用參數傳入 JSON資料,然後利用 SQL 指令轉成資料列存入資料表之中,或是把資料表的資料轉成 JSON的字串,其餘的,大多我都會選擇在應用程式中,使用前端的程式來達成目的。

這樣的應用其實和 SQL Server對於 XML的概念相同,通常把 XML當成參數傳入,以及把資料表轉成 JSON的文字格式就有好多種的應用了。

image

上圖是 SQL Server Document中的圖,可以大概看的出來 JSON在 SQL Server 中的使用方式。

 

SQL Server 2016 對JSON的函數支援

SQL Server 2016對於 JSON的函數支援大概有幾個重要的如下:
1.    使用 JSON_VALUE函數取出 JSON中的單一值。
2.    使用 JSON_QUERY函數取出 JSON中的陣列物件。
3.    使用 ISJSON函數測試是否為 valid JSON(之前提過)。
4.    使用 OPENJSON將 JSON物件轉成 rowset。

當然,也可以利用 JSON_MODIFY這類型的函數修改 JSON內的值(但我認為沒什麼必要學,一輩子大概不太會用幾次)

還是學幾個比較常用的,例如:

使用 OPENJSON函數轉換 JSON集合為 rowset


下列是使用OPENJSON函數將 JSON物件轉成 rowset的寫法:

Declare @json varchar(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處理 XML的方式有點像, SQL Server處理 XML的方式是使用 OPENXML的函數,而 JSON所使用的是 OPENJSON,然後在 WITH後面定義要產生出來的資料表,透過 $.的方式對應到實際 JSON文字的內容。

這裡的 $.有點類似 XML 的 root概念,就是從根去尋找,所以 $.id就是指向到 id的位置,而 $.info.name 則是 info 集合裡的 name,其實只要稍微有一點檔案總管的階層觀念,應該很容易透過範例比對出來。

使用 JSON_VALUE取出單一值


這個函數很像是 XML的 value函數,如何取出 JSON中的單一值,只要利用 JSON_VALUE即可。

下面是 JSON的資料如下:

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

利用
JSON_VALUE ( expression , path ) 

可以取出 JSON中的單一值,你可以將其存入到變數中。

其中 $.info.type,會取出 1的值。
$.info.address.town會取出 Bristol的值依此類推,程式可能寫成像下面這樣:

SET @town = JSON_VALUE(@jsonInfo, '$.info.address.town') 

使用 FOR JSON指令將資料表轉成 JSON文件


有兩種方式將資料表的內容轉成 JSON,和 XML有點像, XML是使用 FOR XML AUTO與 FOR XML RAW、FOR XML PATH,而 JSON是 FOR JSON AUTO 與 FOR JSON PATH。

FOR JSON AUTO就不用講了,因為它沒有辦法調顯示的結果,畢竟是 AUTO的指令,而 PATH則可以。
SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob 
FROM People 
FOR JSON PATH 

以本例而言,id自然就是放在 $底下的 id,而 firstName則是 $.info.name下。

得到的結果如下:


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


結語


SQL Server 2016 之後開始支援 JSON語法,有人問我這是否會代表 JSON會取代原來的 XML,或是說 JSON是否會大流行之類的,我覺得言之過早,因為 SQL Server 2005就開始支援 XML的資料型別了,你有很大量的在 SQL Server中存放 XML的資料嗎?也沒有嘛!

因為這種東西既然出來了,就可能有人會在資料庫中使用,但如果是已經設計好的資料庫,就算有使用 JSON,也是在前端程式中剖析,所以我們還是讓子彈飛一會吧。

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List