目錄

• 目錄
  • 前言
  • 挖坑給自己跳
    • 結構錯誤
    • query 時 where 條件怎麼寫?
    • update 怎麼更新部分就好
    • JSON column 新增欄位時怎麼辦?
  • 總結
  • 參考資料

前言

之前我設計了一個很爛的 table 結構，把圖書館的營業時間、館藏存成JSON

想說這樣後端不用額外多做處理、不用多開欄位，前端撈出來也可以直接用 Array.map 渲染成一個 list 或者 menu，啟不美哉啊

就像下圖這樣

但是後來發現這根本是挖坑給自己跳，接下來就來示範一下我挖過什麼坑吧嗚嗚

挖坑給自己跳

結構錯誤

因為 table 的 column 若型別是JSON，就預設沒有結構、屬性之類的檢查，所以可能會出現這種低級錯誤

雖然還是可以在 query 時用JSON_SCHEMA_VALID()去檢查，但這並不是個理想的方法

query 時 where 條件怎麼寫?

假設要找週四沒有開放(營業時間為 00:00~00:00)的圖書館

SELECT id,
       name,
       JSON_EXTRACT(serviceTimes, '$.THUR') AS ThursdayServiceTime
FROM libraries
WHERE JSON_EXTRACT(serviceTimes, '$.THUR') = "00:00~00:00";

找館藏有id為5且還沒被借出的書籍的圖書館

-> 為JSON_EXTRACT的縮寫

SELECT id,
       name,
       collections
FROM libraries
WHERE JSON_CONTAINS(collections, JSON_OBJECT('id', 5, 'status', 'ready'));

用JSON_CONTAINS去確認collection(是json array)內有沒有id為5，且status為ready的物件，如果有的話會是1(即true)

注意，此情況條件一定要用JSON_OBJECT建立json object，不能直接打，不然被當成字串導致搜不到

但是如果是一個裝了int的json array的話，使用JSON_CONTAINS()搜尋時，

條件必須要用JSON_ARRAY轉型，或者給字串，因為JSON_CONTAINS的條件必須是JSON字串、JSON格式( JSON string or JSON type )

再來個更噁心的

假設要找館藏數大於 1，且當中至少 2 本書被借出的圖書館

SELECT id,
       name,
       JSON_LENGTH(collections) AS collectionCount,
       JSON_EXTRACT(collections, '$[*].status') AS allStatus,
       IFNULL(JSON_LENGTH(JSON_SEARCH(JSON_EXTRACT(collections, '$[*].status'), 'all', 'rent')), 0) AS rentCount
FROM libraries
WHERE JSON_LENGTH(collections) > 1
  AND IFNULL(JSON_LENGTH(JSON_SEARCH(JSON_EXTRACT(collections, '$[*].status'), 'all', 'rent')), 0) >= 2;

update 怎麼更新部分就好?

假設 id 為 9 的圖書館進了一本新書，所以要更新 collections

SET @origin =
  (SELECT collections
   FROM libraries
   WHERE id = 9);

UPDATE libraries
SET collections = JSON_ARRAY_APPEND(@origin, '$', JSON_OBJECT('id', 3, 'status', 'ready'))
WHERE id = 9;

再來個更噁心的

假設 id 為 9 的圖書館的 id 為 3 的書被借出了，所以要更新 collections 中 id 為 3 的書的狀態

這個解法相當的複雜，拆成幾步驟做

1.將json array內的elements拆出來變成一筆一筆的資料

id, name是為了方便查看，之後的步驟會拿掉

SELECT 
     id,
     name,
     bookId
FROM libraries,
     JSON_TABLE(collections, '$[*].id' columns(bookId int PATH '$')) AS jsonTable
WHERE id = 9;

collections是要解析的json data的欄位

'$[*]'代表提取出collections的內的每個物件的所有key，如果只要id則改成'$[*].id'

COLUMNS則是把取出的key的value排成一個一個的column，column的名稱、型別、限制需要自行定義

這個例子中是把column命名做result，型別是JSON，
PATH '$'則是指定名為result的columnvalue要設為所有key的value，如果只要id就改成PATH '$.id'

2.取得目標物件的id

SELECT 
       JSON_SEARCH(JSON_ARRAYAGG(CAST(bookId AS char)), 'all', '3') as targetIndex
FROM libraries,
     JSON_TABLE(collections, '$[*].id' columns(bookId int PATH '$')) AS jsonTable
WHERE id = 9;

JSON_ARRAYAGG用於把bookId聚合起來變成一個陣列，再來搜尋

CAST是用於轉換型別，一定要用CAST把id轉型，不然JSON_SEARCH會搜尋不到

3.使用targetIndex去取得物件，然後更改物件status，以取得狀態為rent的新資料

SET @targetIndex = (SELECT 
       JSON_SEARCH(JSON_ARRAYAGG(cast(bookId AS char)), 'all', '3') as targetIndex
FROM libraries,
     JSON_TABLE(collections, '$[*].id' columns(bookId int PATH '$')) AS jsonTable
WHERE id = 9);

SELECT 
    JSON_SET(JSON_EXTRACT(collections, JSON_UNQUOTE(@targetIndex)), '$.status', 'rent')
FROM libraries
WHERE id = 9;

一定要用JSON_UNQUOTE，不然targetIndex會被當成純字串，然後就取不到物件

4 使用newData和targetIndex去正式更新

SET @targetIndex = (SELECT 
       JSON_SEARCH(JSON_ARRAYAGG(cast(bookId AS char)), 'all', '3') as targetIndex
FROM libraries,
     JSON_TABLE(collections, '$[*].id' columns(bookId int PATH '$')) AS jsonTable
WHERE id = 9);

SET @newData = (SELECT JSON_SET(JSON_EXTRACT(collections, JSON_UNQUOTE(@targetIndex)), '$.status', 'rent')
FROM libraries
WHERE id = 9);

UPDATE libraries
set collections = JSON_SET(collections, JSON_UNQUOTE(@targetIndex), CAST(@newData AS JSON))
where id = 9;

JSON column 新增欄位時怎麼辦?

假設館藏的資訊不再只記錄id、status，還需要多一個attachmentIds來記錄附件的id

千萬不樣想偷懶用JSON_SET直接處理，MySQL只會報錯說in this situation, path expression may not contain *

1.將json array內的elements拆出來變成一筆一筆的資料

先透過JSON_TABLE function來把json轉為table，再用JSON_SET一筆一筆插入attachmentId

然後用group by＋JSON_ARRAYAGG把資料依據id來組裝成像拆開前的樣子

SELECT 
    id,
    JSON_ARRAYAGG(JSON_SET(result, '$.attachmentId', CAST(RAND(10)*10 + 1 AS DECIMAL(0)))) as bookInfo

FROM libraries, JSON_TABLE(collections, '$[*]' COLUMNS (result JSON PATH '$')) AS jsonTable
GROUP BY id;

2.把要更新的table和資料來源的table(前一步的得到的query result) join起來，然後用條件來指定每個row的collections的值

資料來源的table一定要取別名不然沒辦法join，id也要取別名，不然會MySQL認不出id是2個table中哪個table的column(“id is ambitious")

UPDATE libraries
INNER JOIN
  (SELECT id AS libId,
          JSON_ARRAYAGG(JSON_SET(RESULT, '$.attachmentId', CAST(RAND(10)*10 + 1 AS DECIMAL(0)))) AS bookInfo
   FROM libraries,
        JSON_TABLE(collections, '$[*]' COLUMNS (RESULT JSON PATH '$')) AS jsonTable
   GROUP BY libId) dTable ON dTable.libId = libraries.id
SET libraries.collections = dTable.bookInfo;

總結

最後來統整一下使用 JSON column 的優缺點

缺點

1.存在JSON的話要先parse才能進行聚合、篩選
2.JSON沒有固定的結構，(如果不parse)無法確保資料欄位一致
3.在JSON內的欄位無法使用SQL的約束
4.在JSON內的欄位無法定義型別
5.難使用index
6.對JSON內的欄位使用聚合函數、搜尋函數
7.當資料庫規模變大、有新欄位時，可能沒更新到舊資料，而導致出現髒資料
8.不好進行資料庫正規化

優點

如果你懶得開欄位、設約束，而且資料庫以後不是你維護的話，這麼做確實超快

參考資料

Don’t Use JSON Columns in MySQL Here’s Why !
The JSON Data Type in MySQL: Pluses and Minuses
Conditionally select values from an array in a nested JSON string in a Mysql database
Query a JSON column with an array of object in MySQL
JSON_ARRAYAGG(col_or_expr)
MySQL update multiple rows using arrays