MySQLでJSON型を使用する

はじめに

MySQLではバージョン5.7からJSONをそのまま登録できる「JSON型」が使える様になりました。
この記事ではJSON型の使い方についてまとめていきたいと思います。

テーブルを定義する

JSON型を使ってテーブルを作成します。

CREATE TABLE STORE_TBL (
   ID    INT,
   STORE JSON,
   PRIMARY KEY (ID)
);

JSONデータを登録する

作成したテーブルにデータを登録します。

INSERT INTO STORE_TBL VALUES (1, '{"store_cd":"001", "store_nm":"東店", "staff":{"fulltime":["sato", "hayashi"], "parttime":["shimizu", "ogawa"]}}');
INSERT INTO STORE_TBL VALUES (2, '{"store_cd":"002", "store_nm":"北店", "staff":{"fulltime":["suzuki", "kimura"], "parttime":["nakamura"]}}');
INSERT INTO STORE_TBL VALUES (3, '{"store_cd":"003", "store_nm":"西店", "staff":{"fulltime":["tanaka", "sasaki"]}}');

ID	STORE_CD
1	{"staff": {"fulltime": ["sato", "hayashi"], "parttime": ["shimizu", "ogawa"]}, "store_cd": "001", "store_nm": "東店"}
2	{"staff": {"fulltime": ["suzuki", "kimura"], "parttime": ["nakamura"]}, "store_cd": "002", "store_nm": "北店"}
3	{"staff": {"fulltime": ["tanaka", "sasaki"]}, "store_cd": "003", "store_nm": "西店"}

JSONデータを検索する

JSON_EXTRACT関数を使用して特定のkeyの値を取得します。
SELECT JSON_EXTRACT(column, '$.key') FROM TABLE;

mysql> SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL;
+-----------------------------------+
| JSON_EXTRACT(STORE, '$.store_nm') |
+-----------------------------------+
| "東店"                            |
| "北店"                            |
| "西店"                            |
+-----------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(STORE, '$.staff.fulltime') FROM STORE_TBL;
+-----------------------------------------+
| JSON_EXTRACT(STORE, '$.staff.fulltime') |
+-----------------------------------------+
| ["sato", "hayashi"]                     |
| ["suzuki", "kimura"]                    |
| ["tanaka", "sasaki"]                    |
+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(STORE, '$.staff.fulltime[0]') FROM STORE_TBL;
+--------------------------------------------+
| JSON_EXTRACT(STORE, '$.staff.fulltime[0]') |
+--------------------------------------------+
| "sato"                                     |
| "suzuki"                                   |
| "tanaka"                                   |
+--------------------------------------------+
3 rows in set (0.00 sec)

JSON_EXTRACT関数を使用せずに、->"$.key"の形式で取得することも可能です。
SELECT column->"$.key" FROM TABLE;

mysql> SELECT STORE->"$.store_nm" FROM STORE_TBL;
+---------------------+
| STORE->"$.store_nm" |
+---------------------+
| "東店"              |
| "北店"              |
| "西店"              |
+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT STORE->"$.staff.fulltime" FROM STORE_TBL;
+---------------------------+
| STORE->"$.staff.fulltime" |
+---------------------------+
| ["sato", "hayashi"]       |
| ["suzuki", "kimura"]      |
| ["tanaka", "sasaki"]      |
+---------------------------+
3 rows in set (0.00 sec)

mysql> SELECT STORE->"$.staff.fulltime[0]" FROM STORE_TBL;
+------------------------------+
| STORE->"$.staff.fulltime[0]" |
+------------------------------+
| "sato"                       |
| "suzuki"                     |
| "tanaka"                     |
+------------------------------+
3 rows in set (0.00 sec)

ダブルクォートを取り除いて取得したい場合は、JSON_UNQUOTE関数を使用します。

mysql> SELECT JSON_UNQUOTE(STORE->"$.store_nm") FROM STORE_TBL;
+-----------------------------------+
| JSON_UNQUOTE(STORE->"$.store_nm") |
+-----------------------------------+
| 東店                              |
| 北店                              |
| 西店                              |
+-----------------------------------+
3 rows in set (0.00 sec)

JSONを整形して取得したい場合は、JSON_PRETTY関数を使用します。

mysql> SELECT JSON_PRETTY(STORE) FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '001';
+----------------------------------------------+
| JSON_PRETTY(STORE)                           |
+----------------------------------------------+
| {
  "staff": {
    "fulltime": [
      "sato",
      "hayashi"
    ],
    "parttime": [
      "shimizu",
      "ogawa"
    ]
  },
  "store_cd": "001",
  "store_nm": "東店"
}   |
+----------------------------------------------+
1 row in set (0.00 sec)

特定のkeyと値のデータを取得する場合は、JSON_CONTAINS関数を使用します。
JSON_CONTAINS(column, '値', '$.key')　　※指定したKeyと値がある場合は1、ない場合は0を返す

-- staff.fulltimeに"kimura"の値があるstore_nmを取得する
mysql> SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL WHERE JSON_CONTAINS(STORE, '"kimura"', '$.staff.fulltime');
+-----------------------------------+
| JSON_EXTRACT(STORE, '$.store_nm') |
+-----------------------------------+
| "北店"                            |
+-----------------------------------+
1 row in set (0.00 sec)

特定のパスを含むデータを取得する場合は、JSON_CONTAINS_PATH関数を使用します。
JSON_CONTAINS_PATH(column, one_or_all, '$.key')　　※指定したパスがある場合は1、ない場合は0を返す

-- staff.parttimeの指定があるstore_nmを取得する
mysql> SELECT JSON_EXTRACT(STORE, '$.store_nm') FROM STORE_TBL WHERE JSON_CONTAINS_PATH(STORE, 'one', '$.staff.parttime');
+-----------------------------------+
| JSON_EXTRACT(STORE, '$.store_nm') |
+-----------------------------------+
| "東店"                            |
| "北店"                            |
+-----------------------------------+
2 rows in set (0.00 sec)

JSONデータを更新する

JSON_SET関数を使用して特定のkeyの値を更新します。

-- 更新前のデータを表示
mysql> select JSON_EXTRACT(STORE, '$.staff') FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '002';
+--------------------------------------------------------------+
| JSON_EXTRACT(STORE, '$.staff')                               |
+--------------------------------------------------------------+
| {"fulltime": ["suzuki", "kimura"], "parttime": ["nakamura"]} |
+--------------------------------------------------------------+
1 row in set (0.01 sec)

-- store_cd="002"のstaff.parttimeを'saito'に更新する
mysql> UPDATE STORE_TBL SET STORE = JSON_SET(STORE, '$.staff.parttime[0]', 'saito') WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 更新後のデータを表示
mysql> select JSON_EXTRACT(STORE, '$.staff') FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '002';
+-----------------------------------------------------------+
| JSON_EXTRACT(STORE, '$.staff')                            |
+-----------------------------------------------------------+
| {"fulltime": ["suzuki", "kimura"], "parttime": ["saito"]} |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

JSON_ARRAY_APPEND関数を使用して配列の最後に値を追加します。

-- 追加前のデータを表示
mysql> select JSON_EXTRACT(STORE, '$.staff') FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '002';
+-----------------------------------------------------------+
| JSON_EXTRACT(STORE, '$.staff')                            |
+-----------------------------------------------------------+
| {"fulltime": ["suzuki", "kimura"], "parttime": ["saito"]} |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

-- store_cd="002"のstaff.parttimeに'nakano'を追加する
mysql> UPDATE STORE_TBL SET STORE = JSON_ARRAY_APPEND(STORE, '$.staff.parttime', 'nakano') WHERE JSON_EXTRACT(STORE, '$.store_cd') = "002";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 追加後のデータを表示
mysql> select JSON_EXTRACT(STORE, '$.staff') FROM STORE_TBL WHERE JSON_EXTRACT(STORE, '$.store_cd') = '002';                         
+---------------------------------------------------------------------+  
| JSON_EXTRACT(STORE, '$.staff')                                      |
+---------------------------------------------------------------------+
| {"fulltime": ["suzuki", "kimura"], "parttime": ["saito", "nakano"]} |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

まとめ

MySQLにはJSONを使う為の様々な関数が用意されており、これらの関数を使用することで、かなり柔軟にデータを操作することが可能になりました。
今回紹介した関数以外にも多くの関数がありますので、興味のある方は、MySQLの公式ドキュメントをご参照ください。