2022/06/25
JSONを取り扱うならNoSQL使うかしかないのか
先にまとめ
- 画像でネタバレしてますが、SQLでもJSON扱えます。
- 数十年使われて技術的に枯れてると思われてるSQLでもいろいろと進化してるので、普段使ってるものも改めて見直してみるといろいろ発見があるかも？
本題
Webエンジニア歴〇〇年のあなたはある日上司から、APIから取得したユーザー情報を保存できるDBの設計を依頼されました。
ユーザーには連番のID、名前、そしていろいろな情報が入ったJSONの3つの要素があるそうです。ということで、さっそく次のようなテーブルを作成して、テストデータを登録しました。
|
1 2 3 4 5 6 7 8 9 10 | sqlite> CREATE TABLE user( ...> id INT NOT NULL PRIMARY KEY, ...> name TEXT, ...> details JSON ...> ); sqlite> INSERT INTO user VALUES(1, 'Alice', '{ "age": 20, "tel": "0120-xxxxx-yyyy" }'); sqlite> INSERT INTO user VALUES(2, 'Bob', '{ "age": 30, "tel": "0120-xxxxx-zzzzz" }'); sqlite> SELECT * FROM user; 1|Alice|{ "age": 20, "tel": "0120-xxxxx-yyyy" } 2|Bob|{ "age": 30, "tel": "0120-xxxxx-zzzzz" } |
テストデータを上司に見せたところ、ユーザーの中で20歳の人だけを抽出したいと言われました。早速LIKE文を使ったSQLを書いてみますが、20歳ではないBobのデータの電話番号にも20が含まれているため、うまく絞り込めません。さて困ったぞ・・
|
1 2 3 | sqlite> SELECT * FROM user WHERE details LIKE '%20%'; 1|Alice|{ "age": 20, "tel": "0120-xxxxx-yyyy" } 2|Bob|{ "age": 30, "tel": "0120-xxxxx-zzzzz" } |
こんなときはJSON関数を使いましょう。今回のケースだとJSON_EXTRACT関数が使えます。この関数はJSONから必要なフィールドだけを取り出せます。これを使って年齢のフィールドの値だけを取り出せば後は簡単です。
|
1 2 | sqlite> SELECT * FROM user WHERE JSON_EXTRACT(details, '$.age') = 20; 1|Alice|{ "age": 20, "tel": "0120-xxxxx-yyyy" } |
うまくいきました！めでたしめでたし。
あとがき
今回の例はSQLiteを使って作成しました。MySQL(5.7以降)でも同じ関数が使えるようです。その他のDBでは別の関数名で同等の機能が提供されていたりするため、各自でマニュアル等を確認いただければと思います。
参考情報：
https://www.json.org/json-en.html
https://www.sqlite.org/json1.html#jex
https://dev.mysql.com/doc/refman/8.0/ja/json-search-functions.html
Author Profile
SHARE
