如果資料表某個欄位儲存的資料格式是 JSON，想要透過這個 JSON 的某個屬性去過濾資料，要怎麼做呢? 舉例來說，我有一張表用來儲存使用者資訊如下圖。 ``` | name | detail | | ----- | ------------------------------- | | Kevin | {"gender": "male", "age": 23} | | Amy | {"gender": "female", "age": 25} | | Alice | {"gender": "female", "age": 20} | ``` 目標 : 找出整張表裡面性別是女性的使用者資料。 --- 首先，先建立一張測試用的表格。 ```sql create table user( name varchar(50), detail json ); ``` 加一些測試用資料進去。 ```sql insert into user values ('Kevin', '{"gender": "male", "age": 23}'), ('Amy', '{"gender": "female", "age": 25}'), ('Alice', '{"gender": "female", "age": 20}'); ``` 運用不帶 where 條件的搜尋語句，把全部資料都列出來。 ```sql select * from user; ``` ``` | name | detail | | ----- | ------------------------------- | | Kevin | {"gender": "male", "age": 23} | | Amy | {"gender": "female", "age": 25} | | Alice | {"gender": "female", "age": 20} | ``` 我們的目標是想要用 detail 欄位裡面 JSON 格式的 gender 屬性來過濾資料，這個時候可以使用內建的 JSON_CONTAINS() 函數，有找到指定資料時會返回 1 ，沒找到時會返回 0。 `JSON_CONTAINS(欄位名稱, 值, 用來過濾資料的屬性)` 因此，要過濾出 gender 是 female 的資料， sql 指令可以這樣下 : ```sql -- 方法一 select * from user where JSON_CONTAINS(detail, '"female"', '$.gender'); -- 方法二 select * from user where JSON_CONTAINS(detail, '{"gender": "female"}'); ``` 注意！ 因為 JSON_CONTAINS 的第二個參數需要放字串形式的資料，但如果資料本來就是以字串形態存在，如上圖的 female，則除了原本字串的引號外，需要另外再多加一層引號才能正常運作。 ```sql select * from user where JSON_CONTAINS(detail, '23', '$.age'); ``` 如果改成用 age 去搜尋，因為原本是數字格式，就只需要加單層引號即可。 --- 補充資訊一 JSON_CONTAINS 的第一個參數，其實只要是 JSON 格式的資料都可以放。 ```sql SET @json = '{"A": 0, "B": {"C": 1}, "D": 2}'; SELECT JSON_CONTAINS(@json, '2', '$.A'); ``` 也就是說，除了針對資料表的欄位值，其實也可以針對一個定義好的變數進行搜尋，上述的指令會得到以下結果。 ``` | JSON_CONTAINS(@json, '2', '$.A') | | -------------------------------- | | 0 | ``` --- 補充資訊二 我把 user 這張資料表改成用來記錄學生有選修哪些課堂，detail 裡面變成陣列裡面裝多個 JSON 格式的資料。 ``` | name | detail | | ----- | ----------------------------------------------------------------------------- | | Jack | [{"classNo": 1, "className": "Math"}, {"classNo": 2, "className": "Art"}] | | Grace | [{"classNo": 1, "className": "Math"}, {"classNo": 3, "className": "Sport"}] | | Marry | [{"classNo": 3, "className": "Sport"}, {"classNo": 4, "className": "Nature"}] | ``` 我想找出有選修體育課的使用者，指令可以這樣下 : ```sql select * from user where JSON_CONTAINS(detail, '{"className": "Sport"}'); -- 注意！這邊用以下寫法是無效的，因為最上層的陣列本身並不包含 className 屬性 select * from user where JSON_CONTAINS(detail, '"Sport"', '$.className'); ``` --- 以上就是這次的分享，MySQL/ MariaDB 裡面還有很多內建的函數可以使用，有機會大家也可以自己操作看看~ 最後偷偷放個 [Medium](https://medium.com/@HeronCheng/mysql-mariadb-%E4%B9%8B-json-%E6%AC%84%E4%BD%8D%E5%80%BC%E6%9F%A5%E8%A9%A2-f098b9768490) 連結。