🗂️

MySQLでJSON型を使用する

nmatsu

2022/11/16に公開

MySQL

JSON

tech

はじめに

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の公式ドキュメントをご参照ください。

レスキューナウテックブログPublication

日本で唯一の危機管理情報を専門に取り扱う防災・BCPの専門企業、(株)レスキューナウです。当社で活躍するエンジニアの技術ブログを中心に公開していきます。