Go to Qiita Advent Calendar 2024 Top
LoginSignup
13
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

MySQLAdvent Calendar 2022

Day 1
@4th

MySQLでJSON型データを扱ってみる

Last updated at Posted at 2022-11-30

前提

  • MySQL徹底入門 第4版(MySQL8.0対応)の 4章の内容に基づきます。
  • 社内勉強会用の資料なので基本箇条書きとなっています。
  • 一部内容に公式サイトなどからの引用を行っています。

JSONとは

  • 「 J ava S cript O bject N otation」の略称。

JavaScriptはプログラミング言語の1つで、Object Notationは人間が容易に読み書きしながらデータを簡単に扱えるようにフォーマットとし て構造化した記述方法。

  • JSONはテキストをベースにした、軽量なデータ交換をおこなうためのフォーマット。(JavaScriptの為に作られたので名前に入っているが、のちに他言語でも広く使われるようになった。)
  • WebAPIなど、値をサービス間で受け渡しする開発をする際は必ずと言っていいほど目にする。

Wikipedia

JSONのメリット

  • テキスト文字列で記述できる。
  • RDBの様にカラムの設計に中身のデータを併せるという制約がない。
  • 自由な項目数。全ての行が同じ項目を持たなくてよい。
  • 自由な型指定。文字列/数値/配列をミックスできる。キーバリュー式のデータもミックスできる。
    まとめ ： プログラマーがその時の状況に応じて、値や型を追加/削除できるようになっている。

実際にMySQLで使用するJSON形式

  • ①JSON配列
  • JSON 配列は、各データがカンマで区切られ、[ ]に囲まれた以下データ形式で使用する。
["abc", 10, null, true, false]
  • ②JSONオブジェクト
  • JSON オブジェクトは各データがカンマで区切られ、{ } に囲まれたキーと値のペアのセット形式で使用する。
{"k1": "value", "k2": 10}

警告
JSONオブジェクトの各キーは文字列である必要がある。

  • ③JSONのネスト(入れ子構造)
  • JSONのネストは JSON 配列要素および JSON オブジェクトキーの値内で使用することが可能。
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

MySQLにおけるJSONの各使用方法

  • 値の作成
INSERT INTO テーブル名 (カラム名) VALUES
  ( JSON_OBJECT('カラム名', '値',   'カラム名', '値')  )
, ( JSON_OBJECT('カラム名', '値',   'カラム名', '値')  )
, ( '{"カラム名":"値",  "カラム名":"値"}' )
, ( '{"カラム名":"値",  "カラム名":"値"}' );

ポイント
上記2パターンの様にJSON_OBJECT関数を使用してもJSON値を{}で直書きしても、同結果で作成される。(後述)

  • 値の抽出
 SELECT JSON_EXTRACT("カラム名", '値', '値2') FROM テーブル名;

ポイント
JSON_EXTRACT関数を使用してJSON文字列から値を抽出できる。(MySQL 5.7.8以降で対応)(後述)

  • JSON配列の追加
UPDATE テーブル名 SET カラム名 = JSON_INSERT(カラム名, '$.追加したいカラム名', JSON_ARRAY("追加したい名前", "追加したい値") )

ポイント
JSON_ARRAY関数を使用してJSONの配列を作成し、JSON_INSERT関数を使用して値を追加できる。(後述)

  • JSON配列の指定
SELECT JSON_EXTRACT('["a","b","c"]', '$[2]');
+---------------------------------------+
| JSON_EXTRACT('["a","b","c"]', '$[2]') |
+---------------------------------------+
| "c"                                   |
+---------------------------------------+
1 row in set (0.01 sec)

ポイント
通常は'$.a'のように「名前」を指定して値を取り出せるがJSON配列の場合は上記のように指定方法が異なる。

  • '$[2]'は配列の3番目という意味合い。配列のindexは0からスタートする為。(0, 1, 2, 3...)。(後述)
  • JSON配列からの値の削除
UPDATE テーブル名 SET カラム名=JSON_REMOVE(カラム名, '$.名前[インデックス]')

ポイント
JSON_REMOVE関数を使用してJSONの要素を削除できる。(後述)

  • JSONの上書き
1, SELECT JSON_INSERT(カラム名, '$.名前', "値") FROM テーブル名
2, SELECT JSON_REPLACE(カラム名, '$.名前', "値") FROM テーブル名
3, SELECT JSON_SET(カラム名, '$.名前', "値") FROM テーブル名

ポイント
上記1~3の関数を使用してJSONの要素の更新ができるがそれぞれ更新内容に違いがある。(後述)

JSONの使用例

  • テーブルの作成
CREATE TABLE gadget (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  j JSON
) DEFAULT CHARSET=utf8mb4;

ポイント
テーブル作成時にJSONとしてカラム定義しておく必要がある。

  • データのinsert
INSERT INTO gadget (j) VALUES
  ( JSON_OBJECT('name', 'keyboard',  'price', '30000')  )
  , ( JSON_OBJECT('name', 'mouse',  'price', '10000')  )
  , ( JSON_OBJECT('name', 'monitor',  'price', '25000')  )
  , ( '{"name":"desk",  "price":"32000"}' )
  , ( '{"name":"chair",  "price":"6000"}' );
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

ポイント
① ( JSON_OBJECT('name', 'monitor', 'price', '25000') )
② ( '{"name":"monitor", "price":"25000"}' )
上記2パターンの様にJSON_OBJECT関数を使用してもJSON値を直書きしても、同結果となる。

  • テーブル定義を確認する。
DESC gadget;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| j     | json | YES  |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

「j」カラムはしっかりとJSON型で定義されている。

  • 値を確認する
SELECT * FROM gadget;
+----+----------------------------------------+
| id | j                                      |
+----+----------------------------------------+
|  1 | {"name": "keyboard", "price": "30000"} |
|  2 | {"name": "mouse", "price": "10000"}    |
|  3 | {"name": "monitor", "price": "25000"}  |
|  4 | {"name": "desk", "price": "32000"}     |
|  5 | {"name": "chair", "price": "6000"}     |
+----+----------------------------------------+
5 rows in set (0.01 sec)

データのinsertをJSON_OBJECTの使用とJSON値の直書きの2パターンで行ったが結果に変わりがないことが確認できる。

  • 値の抽出
    キー部分のnameとpriceを取り出す。
SELECT JSON_EXTRACT(j, '$.name', '$.price') FROM gadget;
+--------------------------------------+
| JSON_EXTRACT(j, '$.name', '$.price') |
+--------------------------------------+
| ["keyboard", "30000"]                |
| ["mouse", "10000"]                   |
| ["monitor", "25000"]                 |
| ["desk", "32000"]                    |
| ["chair", "6000"]                    |
+--------------------------------------+
5 rows in set (0.08 sec)

警告
SQL文中でJSONを指定するときは、'$.名前' と書く点に注意。

例外) 試しに存在しないカラムで値の抽出を試みる。

+-------------------------------+
| JSON_EXTRACT(j, '$.earphone') |
+-------------------------------+
| NULL                          |
| NULL                          |
| NULL                          |
| NULL                          |
| NULL                          |
+-------------------------------+
5 rows in set (0.00 sec)

全てNULLで返ってくることが分かった。

  • 値の抽出2
    WHEREで取り出す。
SELECT * FROM gadget WHERE j->>'$.name' = 'keyboard';
+----+----------------------------------------+
| id | j                                      |
+----+----------------------------------------+
|  1 | {"name": "keyboard", "price": "30000"} |
+----+----------------------------------------+
1 row in set (0.01 sec)

・WHERE j->>'$.name' = 'keyboard';
の意味合いとしては「j」カラムの、名前が「name」で 値が「keyboard」のレコードを条件指定している。

ポイント
-> は JSON_EXTRACT() の省略記法として書くこともできる。
->> は JSON_UNQUOTE(JSON_EXTRACT()) の省略記法として書くこともできる。
※JSON_EXTRACT → $.名前 のような値をSQLの結果として受け取ったり、条件として与えたい場合に使用。
※JSON_UNQUOTE → SQL文字列からJSON文字列に変換する場合に使用。

  • JSONにJSON配列を追加
UPDATE gadget SET j = JSON_INSERT(j, '$.category', JSON_ARRAY("shop", "amazon") ) WHERE id IN (3,5);
Query OK, 2 rows affected (0.14 sec)
Rows matched: 2  Changed: 2  Warnings: 0

categoryのカラム内にJSON_ARRAYで配列を作成している。

  • 値の確認
SELECT * FROM gadget WHERE id IN (3,5);
+----+-----------------------------------------------------------------------+
| id | j                                                                     |
+----+-----------------------------------------------------------------------+
|  3 | {"name": "monitor", "price": "25000", "category": ["shop", "amazon"]} |
|  5 | {"name": "chair", "price": "6000", "category": ["shop", "amazon"]}    |
+----+-----------------------------------------------------------------------+
2 rows in set (0.01 sec)

JSON配列が追加されていることが確認できる。

例外) 配列部分をJSON_ARRAYではなく、文字列で与えてみる。

SELECT JSON_INSERT('{"a": "xxx"}', '$.c', '["shop", "ヨドバシカメラ"]') as j;
+-----------------------------------------------------+
| j                                                   |
+-----------------------------------------------------+
| {"a": "xxx", "c": "[\"shop\", \"ヨドバシカメラ\"]"} |
+-----------------------------------------------------+
1 row in set (0.01 sec)

先程と違い配列全体がダブルクォーテーションで囲まれているので配列ではなく文字列として認識されている。
この場合の対応策は以下の様にCAST関数を使用して型を変換すればよい。

SELECT JSON_INSERT('{"a":"xxx"}', '$.c', CAST('["shop", "ヨドバシカメラ"]' AS JSON) ) AS j;
+-----------------------------------------------+
| j                                             |
+-----------------------------------------------+
| {"a": "xxx", "c": ["shop", "ヨドバシカメラ"]} |
+-----------------------------------------------+
1 row in set (0.01 sec)

ポイント
CAST(expr AS type)は値を特定の型としてキャストできる。(今回は文字列からJSONへの変換)

  • JSON値削除
    1.削除するデータの事前確認。
SELECT j FROM gadget WHERE id=2;
+-------------------------------------+
| j                                   |
+-------------------------------------+
| {"name": "mouse", "price": "10000"} |
+-------------------------------------+
1 row in set (0.00 sec)

2.削除する為に不要なデータを事前に追加する。

UPDATE gadget SET j = JSON_INSERT(j, '$.place', JSON_ARRAY("akihabara", "kashiwa")) WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


SELECT j FROM gadget WHERE id=2;
+------------------------------------------------------------------------+
| j                                                                      |
+------------------------------------------------------------------------+
| {"name": "mouse", "place": ["akihabara", "kashiwa"], "price": "10000"} |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

買った場所を"place": ["akihabara", "kashiwa"]として秋葉原と柏でJSON配列で持たせてみる。

3.本当は柏で購入したというテイでカテゴリーから秋葉原を削除します。

UPDATE gadget SET j=JSON_REMOVE(j, '$.place[0]') WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT j FROM gadget WHERE id=2;
+-----------------------------------------------------------+
| j                                                         |
+-----------------------------------------------------------+
| {"name": "mouse", "place": ["kashiwa"], "price": "10000"} |
+-----------------------------------------------------------+
1 row in set (0.01 sec)

ポイント
"place": ["akihabara", "kashiwa"], のうち
.place[0]としてインデックス0番の「秋葉原」をJSON配列から削除するように指定している。
※柏を削除するなら'$.place[1]'とする

4.カラム名がplaceだと 何の場所か分かりづらいので"place" -> "buy_shop"にJSONのカラム名を変更したい

UPDATE gadget SET j = JSON_INSERT(JSON_REMOVE(j, '$.place'), '$.buy_shop', JSON_EXTRACT(j, '$.place')) WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> SELECT * FROM gadget WHERE id=2;
+----+--------------------------------------------------------------+
| id | j                                                            |
+----+--------------------------------------------------------------+
|  2 | {"name": "mouse", "price": "10000", "buy_shop": ["kashiwa"]} |
+----+--------------------------------------------------------------+
1 row in set (0.01 sec)

SET= 以降に色々記載があるが、簡単に書くとカラム名の変更の処理は下記流れで行っている。

  1. 既存の名前変更前のカラム(place)を削除する
  2. 削除後の残りのデータに今回名前を変更するカラム(buy_shop)を追加する。
  3. buy_shopの値は現状のplaceの値を用いる
  • JSONの上書き/追加
JSONの値を変更/追加する方法 JSON_INSERT() JSON_REPLACE() JSON_SET()
新規に「名前：値」を追加 する ー する
現存する値を上書き ー する する

↓ つまり

JSON_INSERT() は既存の値を置き換えずに値を挿入する。
JSON_SET() は既存の値を置き換え、存在しない値を追加する。
JSON_REPLACE() は既存の値のみを置き換える。

警告
JSON_INSERT() のみ 既存の値を上書きしないことに注意。

以下簡単に[0, 1] というデータを使用した使用例を記載。

SELECT
  -- あった場合は更新する、ない場合は追加する。↓
  -- [0,1]のうち[1]があれば5に更新、なければ追加。[2]を100に更新、なければ追加。
  JSON_SET('[0,1]', '$[1]', 5, '$[2]', 100) a
  -- あった場合は更新しない、ない場合は追加する。↓
  -- [0,1]のうち[1]があってもば5には更新しない、なければ追加。[2]があっても100には更新しない、なければ追加。
  ,JSON_INSERT('[0,1]', '$[1]', 5, '$[2]', 100) b
  -- あった場合は更新する、ない場合は無視する。↓
  -- [0,1]のうち[1]があれば5に更新、なければ追加しない。[2]を100に更新、なければ追加しない。
  ,JSON_REPLACE('[0,1]', '$[1]', 5, '$[2]', 100) c;

--それぞれ下記データの反映結果を確認
+-------------+-------------+--------+
| a           | b           | c      |
+-------------+-------------+--------+
| [0, 5, 100] | [0, 1, 100] | [0, 5] |
+-------------+-------------+--------+
1 row in set (0.00 sec)
  • JSONのpath記法の種類
'$.name' nameの値
'$[0]' 配列の一番頭を指定
'$.a[1]' {"a":[]}となっているとき、a配列の2番目を指定
'$.*' 名前のワイルドカード(全名前が対象。名前のないものは無視)
'$[*]' 配列のワイルドカード、全配列の要素が対象
'$**' あらゆるパスの中から選び出す

JSON使用時の注意事項

1. JSONカラムにCHARACTER SETを指定してはいけない

  • MySQLのJSON型は強制的にUTF-8で自動変換されて保存される為、JSONカラムに対してCHARACTER SETを指定すると文法エラーとなる。
  1. 現在のテーブル定義
SHOW FULL COLUMNS FROM gadget;
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int  | NULL      | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| j     | json | NULL      | YES  |     | NULL    |                | select,insert,update,references |         |
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.00 sec)

Collation と記載のところが文字コードなので現在は設定がないことが確認できる。

2, JSONカラムに対してUTF8へのエンコーディングを試みる。

ALTER TABLE gadget MODIFY j CHARACTER SET utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET utf8' at line 1

翻訳: SQL 構文にエラーがあります。 1行目の「SET utf8」付近で使用する正しい構文については、MySQLサーバーのバージョンに対応するマニュアルを確認してください

構文エラーとは出るものの、明示的にJSONとUTF8の関係性でエラーと言われるわけではないので、個人的にはまりそうポイント高め。

3, 上記の状態で「JSON」から「TEXT」へカラムの型の変更をしつつ同時にUTF8へのエンコーディングを試みる。

ALTER TABLE gadget MODIFY j TEXT CHARACTER SET utf8;
Query OK, 5 rows affected, 1 warning (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 1

警告
上記で発生しているWarnings: 1 について確認。

show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                     |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

翻訳: 「utf8」は現在、文字セット UTF8MB3 のエイリアスですが、将来のリリースでは UTF8MB4 のエイリアスになります。明確にするために、UTF8MB4 の使用を検討してください。

4, 再度テーブル定義を確認。

SHOW FULL COLUMNS FROM gadget;
+-------+------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int  | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| j     | text | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------+------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.00 sec)

型も文字コードもしっかり反映されていることが確認できる。

5, 更にこの状態で文字コードはそのままにUTF8、型だけを「JSON」にする変更を試みる。

ALTER TABLE gadget MODIFY j json ;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

6, 再々度テーブル定義を確認。

SHOW FULL COLUMNS FROM gadget;
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int  | NULL      | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| j     | json | NULL      | YES  |     | NULL    |                | select,insert,update,references |         |
+-------+------+-----------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.00 sec)

文字コードがNULLに戻ってる。。
なので、結果的にJSONカラムに対してCHARACTER SETを指定してはいけない。

2. JSON型のカラムには一般的にJSONパースできない値が登録される

  • 例えばこれまでのテーブルを用いてnameカラムの値のみをSQLの結果として受け取るかSQLの条件に与えたい場合、下記の様に取得すると文字列取得時にダブルクォーテーションがつくため、そのままではSQL検索や加工に使用できない。

1, 値の確認。

SELECT JSON_EXTRACT(j, '$.name') FROM gadget;
+---------------------------+
| JSON_EXTRACT(j, '$.name') |
+---------------------------+
| "keyboard"                |
| "mouse"                   |
| "monitor"                 |
| "desk"                    |
| "chair"                   |
+---------------------------+
5 rows in set (0.00 sec)

2, そのままの使用を試みる。

SELECT CHAR_LENGTH(JSON_EXTRACT(j, '$.name')) FROM gadget;
+----------------------------------------+
| CHAR_LENGTH(JSON_EXTRACT(j, '$.name')) |
+----------------------------------------+
|                                     10 |
|                                      7 |
|                                      9 |
|                                      6 |
|                                      7 |
+----------------------------------------+

文字列を返す関数を使ってみたが「" "」の2文字分もしっかりカウントされて返ってきた。(例)keyboard=8文字
確かにこの状態では検索しても加工しても引っかからないので、ダブルクォーテーションが邪魔でパース出来ないことが分かる。

  • 解決策(1)
    専用のJSON_UNQUOTE関数でJSONの値を包むことでパース可能な値が返却される。
SELECT JSON_UNQUOTE( JSON_EXTRACT(j, '$.name') ) FROM gadget;
+-------------------------------------------+
| JSON_UNQUOTE( JSON_EXTRACT(j, '$.name') ) |
+-------------------------------------------+
| keyboard                                  |
| mouse                                     |
| monitor                                   |
| desk                                      |
| chair                                     |
+-------------------------------------------+
5 rows in set (0.01 sec)
  • 解決策(2)->解決策(1)を簡潔に記述したもので(1)と同義。(MySQL8.0から対応可能)
SELECT j->>'$.name' FROM gadget;
+--------------+
| j->>'$.name' |
+--------------+
| keyboard     |
| mouse        |
| monitor      |
| desk         |
| chair        |
+--------------+
5 rows in set (0.01 sec)

これは カラム名->>JSON 部分指定の構文となっており、この記述のみで上述した専用関数のJSON_UNQUOTE関数を用いてUNQUOTEした状態の値が得られる。
また、以下の様に「->>」を「->」に変更するとJSON文字列表記(ダブルクォーテーションあり)で返ってくる。

SELECT j->'$.name' FROM gadget;
+-------------+
| j->'$.name' |
+-------------+
| "keyboard"  |
| "mouse"     |
| "monitor"   |
| "desk"      |
| "chair"     |
+-------------+
5 rows in set (0.00 sec)

感想

  • 正直MySQLではJSONを使ったことは今までなかったので良い勉強になった。
  • JSON形式を加工や検索に必要なSQLデータに変換するケース自体は多そうだが、それには変換作業のワンクッションが入るのが少しまどろっこしく感じた。
  • JSONのpath記法が中々読み解くのが難しかった。
13
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
13
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?